Get last X rows order by asc

Question

I want to select last X rows from a table, so what I did was:

SELECT * FROM table ORDER BY id DESC LIMIT 120

and it worked, but I want it to be ordered ascending, so I tried with:

SELECT * FROM table ORDER BY id DESC LIMIT 120 ORDER BY id ASC

expecting that it would reorder selected rows, but it didn't work.

So how can I achieve it?

Answer 1

Even this will work:

( SELECT ... ORDER BY ... LIMIT ... ) ORDER BY ...;

That is, no need in the syntax for the outer SELECT.

Answer 2

You might be able to do something like this.

SELECT t.* 
FROM (SELECT * FROM table ORDER BY id DESC LIMIT 120) AS t
ORDER BY t.id ASC ;

Another alternative would be to load the results into a temp table, then query that for the desired order.