Mysql - return last 3 results in table

Question

Hi, i was wondering if there was an easy way with just an sql statement to return the last three results in the table but in that order i.e. if there are a hundered results it would return in the order of 98, 99, 100 not simply ordering by id DESC and limit 3 which would return in order 100, 99, 98

Any help much appreciated.

p.s. in this instance, lets say I don't know the amount of results and don't really want to send 2 sql requests just to find the amount ( for any OFFSET answers ).

Answer 1

One way would be to use DESC and then just sort them again:

SELECT * FROM (SELECT * FROM some_table ORDER BY id DESC LIMIT 3) a ORDER BY id

Answer 2

Select * 
FROM (SELECT * FROM yourTABLE ORDER BY ID DESC LIMIT 0,3) as TempTable ORDER BY ID

Answer 3

Two options, I guess.

1. You could use DESC to return them in reverse order as you stated, and just reverse the order again in your application code. This is potentially the most efficient, as you can do it in a single query and it can potentially only need to read three rows of an index.

2. You can first find out the number of results in the table, then do a LIMIT <results-3>, 3

Answer 4

Is it okay if you just flip it back to the original order?

SELECT * FROM (SELECT * FROM SOMETABLE ORDER BY ID DESC LIMIT 3) AS T ORDER BY ID;