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

Selecting the Last N Rows from MySQL

Selecting the last N rows from a MySQL database can be a straightforward task using various methods. However, when dealing with primary key columns sorted in ascending order, conventional approaches may encounter limitations. This article aims to provide a reliable solution to this problem, allowing for manipulation and elimination of records within the table.

The Problem:

The original goal is to retrieve the last 50 rows from a table sorted in ascending order based on the primary key column id. However, the initial attempt highlighted two weaknesses:

1. Sorting the rows in descending order using ORDER BY id DESC fails to retain the desired ASC order when using LIMIT 50.
2. Queries relying on subtracting a fixed value from the maximum ID (e.g., id > (SELECT MAX(id) FROM chat) - 50) become unreliable when rows are manipulated.

The Solution:

To overcome these challenges, a more robust approach employs a sub-query:

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

Explanation:

• The sub-query ((SELECT * FROM table ORDER BY id DESC LIMIT 50)) retrieves the last 50 rows from the table while preserving the descending order.
• The outer query (SELECT * FROM ...) wraps the sub-query and introduces a new ORDER BY clause, which overrides the previous sorting and reorders the rows in ascending order based on id.

This approach effectively addresses the limitations encountered with conventional methods. It allows for:

• Retrieval of the last N rows in ASC order, regardless of manipulation or deletions.
• Handling of changes to the table, such as the addition or removal of rows, without affecting the accuracy of the query.

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