Gram Gram - 6 months ago 12
SQL Question

SELECT with Limit in SQL messing up order of rows

I am trying to get some rows from SQL and order them by ID. Currently my query is like this:

SELECT * FROM logs ORDER by id ASC;


This will get all my rows and print them vertically in ascending order of id:

1
2
3
4 etc etc.


Now, the problem is I would like only the bottom 2 to be printed, like this:

3
4


But I have tried different pieces of code and it won't work. To give you an example, I tried:

SELECT * FROM logs ORDER by id ASC DESC limit 10


But this will print this, so the order is wrong again!

4
3


Any ideas? Thanks guys

Answer

You can use a subquery.

SELECT * 
FROM (
    SELECT * 
    FROM logs 
    ORDER BY id DESC 
    LIMIT 2
) subquery 
ORDER BY id ASC

Please note that selecting from subqueries requires an alias (in this case subquery). Otherwise you'll get error Every derived table must have its own alias, at least in MySQL.

Comments