CODEWITHSUNDEEP
phpmysql
user3005687
user3005687

Reputation: 81

How to return last 5 rows by date

How do I return the 5 most recent rows (by date) from a table ordered from oldest to newest.

I.E.

$query = mysqli_query($connect,"SELECT * FROM posts
WHERE postid like '$topic_id'
ORDER BY postdate DESC LIMIT 5");

Returns:

reply 10
reply 9
reply 8
reply 7
reply 6

And:

$query = mysqli_query($connect,"SELECT * FROM posts
WHERE postid like '$topic_id'
ORDER BY postdate ASC LIMIT 5");

Returns:

reply 1
reply 2
reply 3
reply 4
reply 5

How do I return?

reply 6
reply 7
reply 8
reply 9
reply 10

Upvotes: 0

Views: 59

Answers (1)

Barmar
Barmar

Reputation: 780994

Use a subquery to get the most recent rows, then order it the other way in the main query.

SELECT *
FROM (SELECT * FROM posts
      WHERE postid like '$topic_id'
      ORDER BY postdate DESC LIMIT 5) x
ORDER BY postdate ASC

To answer the inevitable question, x is an alias for the subquery, because MySQL requires all subqueries in joins to be named with aliases.

Upvotes: 6

Related Questions