MySQL Query - Show Latest 3 Records with Order by Ascending

Question

Is there anyway to fetch latest 3 comments with Order By id asc ?

Here is my table Structure: Table name: comments

enter image description here

Right Now I am using this Query:

SELECT *
FROM `comments`
ORDER BY id ASC
LIMIT 0 , 3

But it returns in result, which is obvious :

enter image description here

But I want to show latest 3 records , but in Ascending Order. Like this:

enter image description here

possible duplicate of [How to select the last record from MySQL table using SQl syntax](http://stackoverflow.com/questions/2659253/how-to-select-the-last-record-from-mysql-table-using-sql-syntax) — djot, Sep 30 '13 at 08:53
No, it's not a duplicate of that, @djot! That's just about the 1 last record which is much simpler. — markus, Sep 30 '13 at 08:58
@markus Well not directly to this. But duplicate to hundreds of answers here that all show order direction `DESC` for Mysql. — djot, Sep 30 '13 at 09:07

score 8 · Accepted Answer · answered Sep 30 '13 at 08:52

8

Use below code:

SELECT * 
   FROM (SELECT *
      FROM `comments` ORDER BY id DESC LIMIT 0 , 3) t
ORDER BY id ASC;

First you sort by descending id, and get 3 results, and then do ascending sort on id on these 3 results.

answered Sep 30 '13 at 08:52

Rajesh

zonzon · Answer 2 · 2013-09-30T09:03:50.173

7

(SELECT * FROM `comments` ORDER BY id DESC limit 3 ) ORDER BY id ASC

Just reorder the DESC query with a second ORDER BY :)

edited Sep 30 '13 at 09:03

answered Sep 30 '13 at 08:57

zonzon

1

Nice, simple and it works. I never thought of doing that before. – RiggsFolly Sep 30 '13 at 09:04
1

this should be the accepted answer, it avoids a subquery. – Andres SK Mar 21 '14 at 01:57
awesome, took me quite a while to find this solution, thank you!! I must say, I don't really get why ```SELECT * FROM table ORDER BY id DESC, time ASC LIMIT 5``` is not workin? – Can Rau Jan 07 '16 at 02:01

Piyush · Answer 3 · 2013-09-30T09:02:58.930

0

SELECT * FROM (
  SELECT * 
  FROM comments   
  ORDER BY id DESC
  LIMIT 3
) t ORDER by id ASC

edited Sep 30 '13 at 09:02

answered Sep 30 '13 at 08:55

Piyush

score 0 · Answer 4 · answered Sep 30 '13 at 09:08

0

try this

select * from (select * from `comments` ORDER BY id desc limit 0,3) t
order by id asc;

answered Sep 30 '13 at 09:08

Janak Prajapati

Serhat Akay · Answer 5 · 2013-09-30T08:58:07.637

-3

This should do it:

SELECT *
FROM `comments`
ORDER BY id DESC
LIMIT 0 , 3

edited Sep 30 '13 at 08:58

answered Sep 30 '13 at 08:52

Serhat Akay

Make your answer a good answer by explaining why this will do it? – markus Sep 30 '13 at 08:52
That will only work with his test data, but I bet it wont work with the real data! – RiggsFolly Sep 30 '13 at 08:54
@markus because it orders by comment and bottom-up so new ones comes first. – Serhat Akay Sep 30 '13 at 08:55
What if the last comment starts with A and the first comment starts with z ? – Hanky Panky Sep 30 '13 at 08:56
But in real world you need more columns to that table such as status to make it to disable and enable comment. Date Time field etc. – Serhat Akay Sep 30 '13 at 08:56
Upps ORDER BY id DESC should do it not comment. Thanks @Hanky웃Panky – Serhat Akay Sep 30 '13 at 08:57

5 Answers5