2

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?

3
  • 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. Commented 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. Commented 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
    Commented Dec 1, 2016 at 18:12

2 Answers 2

2

Even this will work:

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

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

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.

4
  • should be order by t.id asc - see stackoverflow.com/a/5912827/1387418
    – Kin Shah
    Commented Dec 1, 2016 at 16:58
  • @Kin order by id asc works fine, too. There is only one table named t at that level. Commented 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
    Commented Dec 1, 2016 at 17:02
  • @ypercubeᵀᴹ yeh it works. I mentioned it just as a good practice :-)
    – Kin Shah
    Commented Dec 1, 2016 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.