CODEWITHSUNDEEP
sqlsqlite
ABCplus
ABCplus

Reputation: 4021

SQLite: LIMIT values depending on a condition

Given the following table:

ID   A    B
-----------
1    6    5
2    3    2
3    5    1
4    5    5
5    9    8
6    1    7

I need to SELECT the first X rows that met the condition (A - B) > 0 So it cannot be a:

SELECT ID FROM TABLE WHERE (A - B) > 0

Otherwise I'd get row with ID 5, while I would like to STOP on row 3.

Is it possible to write a condition in the LIMIT statement? Some like:

SELECT ID FROM TABLE LIMIT (A - B) > 0

How can I do? Are there any alternatives?

Note: when I wrote "the first X rows" I mean an ascending ordering on ID.

UPDATE: I did not explain myself correctly, sorry. I don't know the X value. I said X as unknown variable, depending on table. I rephrase the requirement:

I need to select all the rows (starting from ID 1) until the condition (A - B) > 0 is not met.

Upvotes: 1

Views: 994

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You need to be careful if there are no rows that match the ending condition.

Here is a method that always works:

SELECT t.ID
FROM test t CROSS JOIN
     (SELECT MIN(id) as min_id
      FROM test
      WHERE A <= B
     ) tt
WHERE t.id < tt.min_id OR tt.min_id IS NULL;

Here is a db<>fiddle. (It happens to use MySQL; that is because table creation in SQLite doesn't appear to be working.)

Upvotes: 1

Shawn
Shawn

Reputation: 52354

This sounds like a basic gaps and islands problem where you want to find only the first island. That sort of thing is easy to solve using the window functions added in sqlite 3.25 (And this answer thus won't work with older versions).

Given this table:

CREATE TABLE test(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
INSERT INTO test VALUES(1,6,5);
INSERT INTO test VALUES(2,3,2);
INSERT INTO test VALUES(3,5,1);
INSERT INTO test VALUES(4,5,5);
INSERT INTO test VALUES(5,9,8);
INSERT INTO test VALUES(6,1,7);

this query:

WITH islands AS
  (SELECT id, a, b
        , row_number() OVER (PARTITION BY a - b > 0 ORDER BY id) AS isle_rn
   FROM test)
SELECT id, a, b
FROM islands
WHERE id = isle_rn AND a - b > 0
ORDER BY id;

produces

id          a           b         
----------  ----------  ----------
1           6           5         
2           3           2         
3           5           1

Basically, it splits the table up into two partitions - those where a - b > 0 is true, and those where it's false. Each partition is sorted by id, and each row is then numbered. Finally, only the rows where that number is equal to id are returned - the first row where the condition is false will cause all later rows where it's true to have a different id and row number. The extra check that a - b > 0 is there to catch the case where id 1 is false and no rows should thus be returned.

This does assume that id always starts at 1 and has no gaps. If that's not the case, this variation adds a sequential row number that's used instead of id:

WITH islands AS
  (SELECT id, a, b
        , row_number() OVER (ORDER BY id) AS rn
        , row_number() OVER (PARTITION BY a - b > 0 ORDER BY id) AS isle_rn
   FROM test)
SELECT id, a, b
FROM islands
WHERE isle_rn = rn AND a - b > 0
ORDER BY id;

Upvotes: 0

sabhari karthik
sabhari karthik

Reputation: 1371

You can try this: 

SELECT ID FROM TABLE WHERE ID < (SELECT MIN(ID) FROM TABLE WHERE (A - B) <= 0) ORDER BY ID

Upvotes: 1

Berty_Lurch
Berty_Lurch

Reputation: 86

You were very close. Here is an SQL Fiddle 

SELECT * FROM Table WHERE (A - B) > 0 LIMIT 3

Upvotes: 0

Related Questions