PHP: Order by DESC won't do it?

Question

I have this:

$getWallCommentQuery2 = mysql_query("SELECT * FROM users_wallcomments WHERE wallID = '$displayWall[id]' ORDER BY date DESC LIMIT 2");

    while($show = mysql_fetch_array($getWallCommentQuery2)){
    echo $show["comment"] . " <br> ";
    }

Now, my database:

INSERT INTO `users_wallcomments` VALUES (1, 401, 1, 2, '1284056799', 'test kommentar blabla1');
INSERT INTO `users_wallcomments` VALUES (2, 401, 1, 1, '1284540210', 'test test comment2');
INSERT INTO `users_wallcomments` VALUES (3, 401, 1, 1, '1284540225', 'nr3 kommentar');
INSERT INTO `users_wallcomments` VALUES (4, 401, 1, 1, '1284540237', 'nr4 kommentar');

the fifth field.. (where the numbers starts with 1284..) is date unix timestamp. Now i want to sort it from this, by the query at the top, with limit 2;

It limits to two, but it doesnt sort it out right when i do ORDER by date DESC..

I am receiving 3th(id) as last and 4th(id) above it? And i wish to have 4th at last(because it has the latest datestamp as you also can see, it has 37, and third has 25) but still it sort the 3th as last.

What is wrong?

Answer 1

Change DESC to ASC

A date sorted in descending order (DESC) will always produce the most recent dates first and the earliest dates last.

If you sort your dates in ascending order (ASC) you will get the entry with id of 3 first and then the entry with id of 4 next.

UPDATE If you want only the last 2 rows sorted in date asc, then try this:

SELECT * FROM 
    (SELECT * FROM users_wallcomments WHERE wallID = '$displayWall[id]' ORDER BY date DESC LIMIT 2) recentWallComments
ORDER BY date ASC

Answer 2

SELECT * FROM (
    SELECT * FROM users_wallcomments WHERE wallID = '$displayWall[id]' ORDER BY date DESC LIMIT 2
) t1 
ORDER BY t1.date ASC

this shoudl do the trick.

Answer 3

You could do something like :

SELECT * 
 FROM (
       SELECT *
         FROM users_wallcomments 
         WHERE wallID = '$displayWall[id]'
         ORDER BY date DESC LIMIT 2
      ) AS foo
ORDER BY date ASC