CODEWITHSUNDEEP
mysqlsqlselect
krebas
krebas

Reputation: 45

How to select a specific part of a SQL query result?

I have a MySQL table with a million rows. The result of my query needs to contain 100 rows and has to be ordered by date.

I would now like to add a clause to the query that makes it possible to tell the database to "start the result with the row that comes after the row with the id '5'", for example. My result would therefore include the rows with the ids 4, 3, 2, 6, 8, ...

Are you, dear reader, the sought database wizard who could put me out of my misery? ;)

Query:

SELECT id, type, content, date
FROM my_table
WHERE type = 'FI'
ORDER BY date ASC
LIMIT 100;

Result:

| id | type | content  | date       |
|----|------|----------|------------|
| 7  | FI   | ContentR | 2014-01-01 |
| 9  | FI   | ContentS | 2014-01-13 |
| 1  | FI   | ContentT | 2014-02-09 |
| 5  | FI   | ContentU | 2014-03-27 |
| 4  | FI   | ContentV | 2014-03-30 | ---|
| 3  | FI   | ContentW | 2014-04-01 |    |
| 2  | FI   | ContentX | 2014-06-10 |    |- The result I want
| 6  | FI   | ContentY | 2014-09-03 |    |
| 8  | FI   | ContentZ | 2014-12-09 |    |
 ...                                  ---|

Upvotes: 0

Views: 2370

Answers (1)

Barmar
Barmar

Reputation: 780724

SELECT id, type, content, date
FROM my_table
WHERE type = 'FI'
AND date > (SELECT date FROM my_table WHERE id = 5)
ORDER BY date ASC
LIMIT 100;

Upvotes: 3

Related Questions