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?

You were not downvoted, the question was. Someone (from the 15 that viewed it) thought it could be improved or that it wasn't good enough. — ypercubeᵀᴹ, Dec 1, 2016 at 18:06
I added the mysql tag by the way, based on the LIMIT clause. If you use another DBMS (eg SQLite), please edit accordingly. — ypercubeᵀᴹ, Dec 1, 2016 at 18:08
Yes, the question I mean, and right, I forgot to mention that it was about MySQl, thanks! — Mc Kernel, Dec 1, 2016 at 18:12

Rick James · Accepted Answer · 2016-12-02 22:06:41Z

2

Even this will work:

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

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

answered Dec 2, 2016 at 22:06

Rick James

77.1k5 gold badges45 silver badges111 bronze badges

Add a comment |

ypercubeᵀᴹ · Accepted Answer · 2016-12-01 18:09:25Z

5

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.

ypercubeᵀᴹ

97.4k13 gold badges210 silver badges302 bronze badges

answered Dec 1, 2016 at 16:57

Bob Klimes

3,2041 gold badge17 silver badges30 bronze badges

should be order by t.id asc - see stackoverflow.com/a/5912827/1387418
– Kin Shah
Dec 1, 2016 at 16:58
@Kin order by id asc works fine, too. There is only one table named t at that level.
– ypercubeᵀᴹ
Dec 1, 2016 at 17:01
So I must make a double SELECT right? I was thinking about that or UNION... isn't there any more direct and/or efficient way to do it? thanks
– Mc Kernel
Dec 1, 2016 at 17:02
@ypercubeᵀᴹ yeh it works. I mentioned it just as a good practice :-)
– Kin Shah
Dec 1, 2016 at 17:05

Add a comment |

2 Answers 2