How to Reliably Retrieve the Last N Rows in Ascending Order from a MySQL Table?

Retrieve Last N Rows in Ascending Order from a Manipulable MySQL Table

When retrieving the last N rows from a MySQL database that can potentially be manipulated, ensuring both chronological (descending) and ascending order can be challenging. This article addresses this issue, providing a solution using a subquery.

The following query aims to select the last 50 rows from a table ordered by the primary key id in descending order:

SELECT *
FROM `table`
ORDER BY id DESC
LIMIT 50;

However, this approach has a flaw: if any rows are deleted, the subsequent rows will not be retrieved accurately in ascending order.

Similarly, the query below fails to address the manipulation issue:

SELECT *
FROM `table`
WHERE id > ((SELECT MAX(id) FROM chat) - 50)
ORDER BY id ASC;

Solution using a Subquery:

To overcome these limitations, a subquery can be employed:

SELECT * FROM
(
 SELECT * FROM table ORDER BY id DESC LIMIT 50
) AS sub
ORDER BY id ASC;

This query selects the last 50 rows from the table in descending order using a subquery, and then re-orders them in ascending order before retrieving the final result.

This approach allows for accurate retrieval of the last N rows, regardless of any changes or deletions made to the table, ensuring that the data is displayed in the desired chronological sequence.

The above is the detailed content of How to Reliably Retrieve the Last N Rows in Ascending Order from a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!