I have an SQL table Foo which looks similar to this:
CREATE TABLE Foo (
ID int NOT NULL PRIMARY KEY,
Size int,
);
I have this query:
SELECT * FROM FOO
ORDER BY Size ASC;
I want to limit this query to have N objects after a specific ID in that order.
So e.g. I have this table:
| ID | Size |
|---|---|
| 0 | 4 |
| 1 | 8 |
| 2 | 1 |
| 3 | 3 |
| 4 | 10 |
| 5 | 7 |
This would order to:
| ID | Size |
|---|---|
| 2 | 1 |
| 3 | 3 |
| 0 | 4 |
| 5 | 7 |
| 1 | 8 |
| 4 | 10 |
Now I want the first 3 (N) elements after the ID 3. So the resulting query should return:
| ID | Size |
|---|---|
| 0 | 4 |
| 5 | 7 |
| 1 | 8 |
My solution would be to query the Size where the ID is 3 first and then limit the query to the number N, but I wonder if there is a way to handle this in a single query.
Edit: The size column is not unique an my approach would therefore not work.
CodePudding user response：
Use a WHERE clause with a subquery to only consider rows with a size greater than that of ID 3:
SELECT *
FROM foo
WHERE size > (SELECT size FROM foo WHERE id = 3)
ORDER BY size ASC
LIMIT 3;