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

Retrieving Last N Rows in MySQL in Ascending Order with Manipulated Data

Selecting the last N rows from a MySQL database is a common operation. However, specifying the order in which the rows should be returned can be tricky when the data is being manipulated.

Consider the following scenario: you want to select the last 50 rows from a table named "table" based on the column "id," which is the primary key. Sorting the rows in ascending order by "id" is an essential requirement.

Failed Attempts

The following query attempts to sort the results in descending order, but it fails to return the last 50 rows:

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

Alternatively, this query tries to identify the last 50 rows by comparing the "id" values to the maximum "id" value minus 50, but it doesn't work if rows have been deleted:

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

The Solution

To address this issue, you can use a subquery to achieve the desired result:

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

This query first executes the subquery, which selects the last 50 rows from "table" and orders them in descending order. The resulting subquery is then aliased as "sub."

Finally, the outer query selects all rows from the "sub" subquery and orders them in ascending order. This effectively retrieves the last 50 rows from the table, ensures that they are sorted in ascending order based on the "id" column, and handles the case where rows may have been manipulated.

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