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

Retrieving the Last N Rows from MySQL in Ascending Order

Retrieving the last N rows from a MySQL database can be a common task, especially when working with large tables. However, ordering the results in ascending order while ensuring the integrity of the data, can become a challenge.

Consider the following query:

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

At first glance, this query appears to select the last 50 rows. However, it does so in descending order (from greatest to least), which violates the requirement for ascending order.

Another approach is:

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

This query aims to retrieve rows with IDs greater than the maximum ID minus 50. However, it also fails because the data is subject to manipulation, and rows could be deleted, potentially affecting the results.

Solution: Subquery Approach

To address these challenges, a subquery can be employed:

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

This query involves a subquery that selects the last 50 rows in descending order. The results of this subquery are then stored in a temporary table called "sub." Finally, the outer query selects all rows from "sub" and orders them in ascending order.

This approach ensures that the last 50 rows are selected, even if the table is manipulated, and the results are presented in ascending order.

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