SQLite Forum

Retrieving FIRST occurrence of a string in a column (SQLITE PHP)
Login

Retrieving FIRST occurrence of a string in a column (SQLITE PHP)

(1) By PongoZ on 2020-03-28 20:24:17 [link] [source]

Hi I'm new to SQLITE and PHP and I'm trying to build a simple database.

How can I echo all the variables of a table that are under a specific column heading ("ColumnX")?

Next step, I would need to find the index of the PREVIOUS, the NEXT, the FIRST and the LAST occurrence of a specific unique string ("Hello") in the column.

Cheers!

(2) By Simon Slavin (slavin) on 2020-03-28 21:15:58 in reply to 1 [link] [source]

For your first question:

I think you want something like

SELECT ColumnX FROM MyTable

For your second question:

The rows of SQL tables don't have any order. There's no first row or last row, just a bag full of rows. If you don't specify an ORDER BY clause you could get your rows in any order the software finds convenient. And if you do the same SELECT twice you might get the rows in two different orders.

From your questions I think you need to read a book or a web site which teaches you SQL. You might find this useful:

https://www.w3schools.com/sql/

(4.1) By PongoZ on 2020-03-29 06:33:07 edited from 4.0 in reply to 2 [link] [source]

Thank you Simon. I did not realize that SQL tables' rows have not an order per se. Very important point to understand! I have therefore added a sequential incremental number to identify the order of the entries as they are added to the database.

Let me clarify with an example what I would like to achieve.

MyTable:

progressive_no	        ColumnX
1	                                      1
2 	                                      2
3 	                                      3
4 	                                      4
5 	                                      2
6 	                                      2
7 	                                      5
8		                              2

Let's take string "2" from ColumnX. As you can see it appears in progressive_no 2, 5, 6 and 8.

Let's assume now that I have currently selected the row with progressive_no 5 (string "2" from ColumnX).

Now I need to retrieve at which progressive_no I find the: 

- previous occurrence of string "2" (if a previous entry is present)
- next occurrence of string "2"  (if another entry is present)
- first occurrence of string "2"
- last occurrence of string "2" 

Cheers!

(7) By luuk on 2020-03-29 07:40:49 in reply to 4.1 [link] [source]

CREATE TABLE MyTable (progressive_no int, ColumnX int);

INSERT INTO MyTable(progressive_no, ColumnX) VALUES
    (1,1),
    (2,2),
    (3,3),
    (4,4),
    (5,2),
    (6,2),
    (7,5),
    (8,2);

SELECT 
  t1.ColumnX  ,
  t1.progressive_no,
  MAX(t2.progressive_no) as Previous,
  MIN(t3.progressive_no) as Next,
  MIN(t4.progressive_no) as First,
  MAX(t5.progressive_no) as Last
FROM MyTable t1
LEFT JOIN (
	SELECT progressive_no, ColumnX
	FROM MyTable t2
	ORDER BY t2.progressive_no DESC
	
	) t2 ON t2.ColumnX=t1.ColumnX and t2.progressive_no <t1.progressive_no 
LEFT JOIN (
	SELECT progressive_no, ColumnX
	FROM MyTable t3
	ORDER BY t3.progressive_no ASC
	
	) t3 ON t3.ColumnX=t1.ColumnX and t3.progressive_no >t1.progressive_no 
LEFT JOIN (
	SELECT progressive_no, ColumnX
	FROM MyTable t4
	ORDER BY t4.progressive_no ASC
	
	) t4 ON t4.ColumnX=t1.ColumnX 
LEFT JOIN (
	SELECT progressive_no, ColumnX
	FROM MyTable t5
	ORDER BY t5.progressive_no DESC
	
	) t5 ON t5.ColumnX=t1.ColumnX 
WHERE t1.progressive_no=5
GROUP BY t1.progressive_no,t1.Columnx;

output:
ColumnX     progressive_no  Previous    Next        First       Last
----------  --------------  ----------  ----------  ----------  ----------
2           5               2           6           2           8

(9) By Keith Medcalf (kmedcalf) on 2020-03-29 09:16:07 in reply to 7 [link] [source]

The following returns the same results and is about twice as fast even with only 8 records in MyTable ... and it should always take about the same time no matter how many records are in the table.

CREATE TABLE MyTable
(
    progressive_no integer primary key,
    ColumnX text
);
create index idx on MyTable(ColumnX);

INSERT INTO MyTable (ColumnX) VALUES
    (1),
    (2),
    (3),
    (4),
    (2),
    (2),
    (5),
    (2);

select * from MyTable;

progressive_no  ColumnX
--------------  ----------
1               1
2               2
3               3
4               4
5               2
6               2
7               5
8               2

SELECT t1.ColumnX,
       t1.progressive_no,
       (
            select progressive_no
              from MyTable
             where ColumnX == t1.ColumnX
               and progressive_no < t1.progressive_no
          order by progressive_no desc
             limit 1
       ) as previous,
       (
            select progressive_no
              from MyTable
             where ColumnX == t1.ColumnX
               and progressive_no > t1.progressive_no
          order by progressive_no
             limit 1
       ) as next,
       (
            select progressive_no
              from MyTable
             where ColumnX == t1.ColumnX
          order by progressive_no
             limit 1
       ) as first,
       (
            select progressive_no
              from MyTable
             where ColumnX == t1.ColumnX
          order by progressive_no desc
             limit 1
       ) as last
  from MyTable as t1
 where progressive_no == 5;

ColumnX     progressive_no  previous    next        first       last
----------  --------------  ----------  ----------  ----------  ----------
2           5               2           6           2           8

(8) By Keith Medcalf (kmedcalf) on 2020-03-29 08:00:27 in reply to 4.1 [link] [source]

Records in a table do have a "record number" that reflects the order in which the records were added called the "rowid". You can read about it here https://sqlite.org/rowidtable.html.

If your "progressive_no" is simply a number that is one bigger than the biggest number that exists so far then you can declare your MyTable as:

create table MyTable
(
   progressive_no integer primary key,
   ColumnX text not null
);
create index MyTableIndex on MyTable (ColumnX);

whenever you do an insert like:

insert into MyTable (ColumnX) values (?);

then the new record will automatically be assigned a progressive_no of coalesce(max(progressive_no)+1, 1). This is a 64-bit integer so when the next record (records after # 9223372036854775807) then a "free number" will randomly be chosen if one exists for the new record number. See https://sqlite.org/autoinc.html

(3) By Keith Medcalf (kmedcalf) on 2020-03-28 23:33:38 in reply to 1 [link] [source]

Please define your use of the term "index"
Please define your use of the phrase "of a specific unique string in a column"

By "index" do you mean the rowid?
By first/last/previous/next no you mean that, for example, when looking at the specific row containing 'it is it partially it is wonky' the instance of 'it' WITHIN the data?

Please provide an EXAMPLE of what you are trying to achieve since your problem definition is somewhat ambiguous. If possible please state all conditions and constraints on your problem space.

(5) By PongoZ on 2020-03-29 05:58:25 in reply to 3 [source]

Hi Keith,

Please have a look at my reply above as it clarifies my question. Thanks

(6) By Keith Medcalf (kmedcalf) on 2020-03-29 07:34:35 in reply to 5 [link] [source]

  • previous occurrence of string "2" (if a previous entry is present)
  select progressive_no 
    from MyTable
   where ColumnX == :string
     and progressive_no < :lastno
order by progressive_no desc
   limit 1;
  • next occurrence of string "2" (if another entry is present)
  select progressive_no 
    from MyTable
   where ColumnX == :string
     and progressive_no > :lastno
order by progressive_no 
   limit 1;
  • first occurrence of string "2"
  select progressive_no 
    from MyTable
   where ColumnX == :string
order by progressive_no 
   limit 1;
  • last occurrence of string "2"
  select progressive_no 
    from MyTable
   where ColumnX == :string
order by progressive_no desc
   limit 1;

Where :string is the string value you are looking for ('2' in your example) and :lastno is the progressive_no where you are looking from (5 in your example).

You will want a unique index on (ColumnX, progressive_no).

(10) By PongoZ on 2020-04-01 08:05:05 in reply to 6 [link] [source]

Awesome. It works perfectly. Many thanks!