Mysql - return last 3 results in table

mysql sql-order-by sql-limit
Phil Jackson picture Phil Jackson · Jun 17, 2010 · Viewed 7k times · Source

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

Lukáš Lalinský picture Lukáš Lalinský · Jun 17, 2010

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

Related questions

How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
MySQL order by before group by

There are plenty of similar questions to be found on here but I don't think that any answer the question adequately. I'll continue from the current most popular question and use their example if that's alright. The task in this …

Read More
Order a MySQL table by two columns

How do I sort a MySQL table by two columns? What I want are articles sorted by highest ratings first, then most recent date. As an example, this would be a sample output (left # is the rating, then the article …

Read More