CODEWITHSUNDEEP
mysql
Grego
Grego

Reputation: 2250

How can I finish my MySQL fetch after searching a specific amount of rows even if the row is not found?

I have a MySQL Database with over 100k rows, so I need to make a search to fetch only the last 1000 rows , so if it is not found in the last 1000 rows the fetch ends (even if it is not found)

Example: if my table is like that

id   name
1     AL
2     BL
...
1000  P12
1001  P15

And I do a fetch like this: SELECT * FROM myTable WHERE name = 'AL' ONLY LAST 1000 ROWS ORDER BY id DESC (Since I don't know what to use I invented the ONLY LAST 1000 ROWS)

This should return empty because I wanted my query to get the information only if it was on the last 1000 rows, not on the 1001th as specified.

Using LIMIT field doesn't work as it would LIMIT the FOUND ROWS not when they are not found.

Is there a way to implement this in MySQL ? Thank you!

Upvotes: 2

Views: 70

Answers (1)

jjjjjjjjjjjjjjjjjjjj
jjjjjjjjjjjjjjjjjjjj

Reputation: 3118

As touched on in the comments, you can use OFFSET to get the id of the 1000th last record, then SELECT records with an id larger than that record's id.

Something like this:

SELECT name 
FROM myTable 
WHERE id > (SELECT id FROM myTable ORDER BY id DESC LIMIT 1 OFFSET 1000)
AND name = 'AL'

Upvotes: 1

Related Questions