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

Retrieving Last N Rows from MySQL in ASC Order

When attempting to retrieve the last N rows from a MySQL database, ordering them in ascending order can be tricky. The standard DESC order fails when rows can be manipulated. Similarly, using a WHERE clause to select rows with IDs greater than the maximum ID minus N can fail if rows are deleted.

To overcome these challenges, a sub-query can be used:

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

This query first selects the last 50 rows from the 'table' table in descending order, using the LIMIT clause. The result of this sub-query is stored in the 'sub' table. Finally, the sub-query itself is ordered in ascending order by the 'id' column.

By using a sub-query, the selection of the last N rows is isolated from the ordering process. This ensures that the last N rows are always selected, even if rows are added or deleted from the original table. The sub-query's inner query orders the rows in descending order, while the outer query orders the sub-query's result in ascending order.

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