MySQL SELECT query - last few records but in order.

Hi All

I am a bit stumped on this one hence me asking for help.

I have a comments table with the usual fields, comment_id, comment, poster etc....

What i would like is to display the last 3 comments but in the order that they were posted.

When i set the limit to 3 it only brings me the first 3. When i reverse the comment_id it gives me the last 3 but in the wrong order.

Say i have 10 comments i want to display.......

Click here for all 10 comments
-Comment 8
-Comment 9
-Comment 10

Hope someone can help, thanks.
Alex

 

If you already know the total number of comments (from a previous query), you can use it in your LIMIT clause to accomplish what you're looking for. Here's an example using PHP:

$sql .= "LIMIT " . ($numRows - 3) . ", 3";

PHP:

Otherwise, you can just use a SELECT COUNT(*) type query first to determine $numRows.

 

you can add comment_posted_date field to your table if doesnt have much data already..

 

It should be like this

SELECT * id FROM comments ORDER BY comment_id DESC LIMIT 3

Code (markup):

 

Oops, i misread what you wanted- try a subquery

SELECT * id FROM comments ORDER BY comment_id DESC LIMIT 3

Code (markup):

becomes

SELECT * FROM (
SELECT * id FROM comments ORDER BY comment_id DESC LIMIT 3
) t ORDER BY t.comment_id

Code (markup):