How to Select the Last N Rows in MySQL

by

When working with MySQL databases, you may need to retrieve the last N rows from a table. This is a common requirement in data analysis, reporting, dashboards, application development, and database administration.

For example, you might want to retrieve:

  • The last 10 records inserted into a table
  • The most recent 5 transactions
  • The last 20 orders
  • The latest 100 log entries
  • The last N records based on an ID or timestamp

MySQL provides the ORDER BY and LIMIT clauses that can be combined to accomplish this.

A common pattern for selecting the last N rows and displaying them in ascending order is:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 10
) AS temp
ORDER BY id ASC;

The inner query first sorts the rows in descending order and retrieves the last 10 records. The outer query then sorts those 10 records in ascending order.

Example: How to Select Last N Rows from a Table in MySQL

Suppose we have a table named athletes containing information about basketball players.

We can create the table with:

CREATE TABLE athletes (
    id INT PRIMARY KEY,
    team VARCHAR(50) NOT NULL,
    points INT NOT NULL
);

Next, insert some sample records:

INSERT INTO athletes VALUES
(1, 'Mavs', 22),
(2, 'Mavs', 14),
(3, 'Lakers', 37),
(4, 'Knicks', 19),
(5, 'Warriors', 26),
(6, 'Knicks', 40),
(7, 'Lakers', 21),
(8, 'Celtics', 15),
(9, 'Hawks', 18),
(10, 'Celtics', 23),
(11, 'Jazz', 25),
(12, 'Jazz', 18),
(13, 'Kings', 14);

You can view all rows with:

SELECT *
FROM athletes;

The output is:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  1 | Mavs     |     22 |
|  2 | Mavs     |     14 |
|  3 | Lakers   |     37 |
|  4 | Knicks   |     19 |
|  5 | Warriors |     26 |
|  6 | Knicks   |     40 |
|  7 | Lakers   |     21 |
|  8 | Celtics  |     15 |
|  9 | Hawks    |     18 |
| 10 | Celtics  |     23 |
| 11 | Jazz     |     25 |
| 12 | Jazz     |     18 |
| 13 | Kings    |     14 |
+----+----------+--------+

There are 13 rows in the table.

Select the Last 10 Rows in MySQL

Suppose we want to retrieve the last 10 rows based on the id column.

We can use:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 10
) AS temp
ORDER BY id ASC;

The result is:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  4 | Knicks   |     19 |
|  5 | Warriors |     26 |
|  6 | Knicks   |     40 |
|  7 | Lakers   |     21 |
|  8 | Celtics  |     15 |
|  9 | Hawks    |     18 |
| 10 | Celtics  |     23 |
| 11 | Jazz     |     25 |
| 12 | Jazz     |     18 |
| 13 | Kings    |     14 |
+----+----------+--------+

The last 10 IDs are:

4, 5, 6, 7, 8, 9, 10, 11, 12, 13

The outer query displays them in ascending order.

How the Query Works

The query contains two SELECT statements.

The inner query is:

SELECT *
FROM athletes
ORDER BY id DESC
LIMIT 10;

First, MySQL sorts the rows by id from highest to lowest:

13
12
11
10
9
8
7
6
5
4
3
2
1

Then LIMIT 10 keeps only:

13
12
11
10
9
8
7
6
5
4

The outer query then runs:

SELECT *
FROM (...)
ORDER BY id ASC;

This changes the display order to:

4
5
6
7
8
9
10
11
12
13

Therefore, the query both selects the last 10 rows and displays them in their original ascending order.

Why Use a Subquery?

You could write:

SELECT *
FROM athletes
ORDER BY id DESC
LIMIT 10;

This does select the last 10 rows, but the output is in descending order:

13
12
11
10
9
8
7
6
5
4

If you want those same 10 rows displayed in ascending order:

4
5
6
7
8
9
10
11
12
13

the subquery approach is useful:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 10
) AS temp
ORDER BY id ASC;

The inner query determines which rows are selected, while the outer query determines how those selected rows are displayed.

Select the Last 3 Rows in MySQL

You can change the value after LIMIT to retrieve a different number of rows.

For example, to select the last three rows:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 3
) AS temp
ORDER BY id ASC;

The result is:

+----+-------+--------+
| id | team  | points |
+----+-------+--------+
| 11 | Jazz  |     25 |
| 12 | Jazz  |     18 |
| 13 | Kings |     14 |
+----+-------+--------+

The three highest IDs are 11, 12, and 13, so these are the last three rows according to the id ordering.

Select the Last 5 Rows

To select the last five rows:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 5
) AS temp
ORDER BY id ASC;

The result is:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  9 | Hawks    |     18 |
| 10 | Celtics  |     23 |
| 11 | Jazz     |     25 |
| 12 | Jazz     |     18 |
| 13 | Kings    |     14 |
+----+----------+--------+

Select the Last 20 Rows

If your table contains many records, you can retrieve the last 20 rows with:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 20
) AS temp
ORDER BY id ASC;

If the table contains fewer than 20 rows, MySQL simply returns all available rows.

Select the Last N Rows Using a Datetime Column

The id column is often used when it increases sequentially, but it is not always the best definition of “last.”

For transaction, order, event, and log tables, you may want the last rows based on a timestamp.

Suppose you have:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATETIME NOT NULL
);

To select the last 10 orders based on order_date:

SELECT *
FROM (
    SELECT *
    FROM orders
    ORDER BY order_date DESC, order_id DESC
    LIMIT 10
) AS recent_orders
ORDER BY order_date ASC, order_id ASC;

This approach is generally more meaningful when “last” means most recent by date and time rather than the highest ID.

Why Add a Second ORDER BY Column?

Suppose multiple records have exactly the same timestamp:

2026-08-17 10:30:00
2026-08-17 10:30:00
2026-08-17 10:30:00

The database needs a deterministic way to decide which rows come first.

You can use a unique column such as order_id as a tie-breaker:

ORDER BY order_date DESC, order_id DESC

Then reverse both directions in the outer query:

ORDER BY order_date ASC, order_id ASC

This produces more predictable results.

Select the Last N Rows Based on an ID

If id is an auto-incrementing primary key, the following query is commonly used:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 10
) AS temp
ORDER BY id ASC;

This works well when a larger ID represents a newer record.

However, it is important to understand that an ID does not necessarily represent insertion time in every database design.

For example, records could be imported, deleted, inserted manually, or assigned IDs independently of business timestamps.

If you specifically need the most recent records, use a timestamp such as created_at when appropriate.

Select the Last N Rows Without a Subquery

If you do not care about the final ordering, the query can be much simpler:

SELECT *
FROM athletes
ORDER BY id DESC
LIMIT 10;

This returns the last 10 rows based on id, but in descending order.

For example:

13
12
11
10
9
8
7
6
5
4

Use this version when descending order is acceptable.

Select the Last N Rows in Ascending Order

If you want the last N records but want them displayed chronologically or by increasing ID, use the subquery:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 10
) AS temp
ORDER BY id ASC;

This distinction is important.

The query:

SELECT *
FROM athletes
ORDER BY id DESC
LIMIT 10;

means:

Find the 10 highest IDs and display them highest first.

The subquery version means:

Find the 10 highest IDs, then display those selected records lowest first.

Select the Most Recent N Records

A common real-world requirement is retrieving the most recent records from a table.

For example:

SELECT *
FROM (
    SELECT *
    FROM orders
    ORDER BY order_date DESC
    LIMIT 10
) AS recent_orders
ORDER BY order_date ASC;

This is useful for:

  • Recent orders
  • Recent transactions
  • Latest customer activity
  • Recent website events
  • Latest application logs
  • Recent support tickets
  • Recent sensor measurements
  • Latest financial transactions

Select the Last N Rows with a WHERE Clause

You can also filter records before selecting the last N rows.

For example, suppose you only want the last five athletes with at least 20 points:

SELECT *
FROM (
    SELECT *
    FROM athletes
    WHERE points >= 20
    ORDER BY id DESC
    LIMIT 5
) AS temp
ORDER BY id ASC;

The WHERE clause is evaluated before ORDER BY and LIMIT within the inner query.

Conceptually, MySQL:

  1. Filters records where points >= 20.
  2. Sorts those records by id descending.
  3. Keeps the last five records according to that ordering.
  4. The outer query sorts the selected rows by id ascending.

Select the Last N Rows for a Particular Team

You can also combine filtering with the last-N pattern.

For example, to find the last three records for the Lakers:

SELECT *
FROM (
    SELECT *
    FROM athletes
    WHERE team = 'Lakers'
    ORDER BY id DESC
    LIMIT 3
) AS temp
ORDER BY id ASC;

This is useful when you want the most recent records within a particular category, customer, product, or group.

Select the Last N Transactions for a Customer

A practical example in an e-commerce system is retrieving a customer’s latest transactions:

SELECT *
FROM (
    SELECT *
    FROM transactions
    WHERE customer_id = 101
    ORDER BY transaction_date DESC, transaction_id DESC
    LIMIT 10
) AS recent_transactions
ORDER BY transaction_date ASC, transaction_id ASC;

This retrieves the customer’s 10 most recent transactions and then displays those transactions chronologically.

Select the Last N Orders for Each Customer

Selecting the last N rows overall is different from selecting the last N rows for each group.

For example:

SELECT *
FROM (
    SELECT
        order_id,
        customer_id,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS row_num
    FROM orders
) AS ranked_orders
WHERE row_num <= 3;

This uses the MySQL 8.0 window function ROW_NUMBER() to retrieve the three most recent orders for each customer.

This is a more advanced version of the last-N problem because the limit is applied separately within each customer group.

Using LIMIT with OFFSET

MySQL also supports LIMIT with an offset.

The syntax is:

LIMIT offset, count

For example:

SELECT *
FROM athletes
ORDER BY id DESC
LIMIT 10, 5;

This skips the first 10 rows in descending order and then returns the next five.

However, LIMIT with OFFSET is more commonly associated with pagination than simply retrieving the last N rows.

Common Mistake: Using LIMIT Without ORDER BY

Avoid assuming that:

SELECT *
FROM athletes
LIMIT 10;

will return the first 10 or last 10 rows in a meaningful order.

Without an ORDER BY clause, SQL does not guarantee a particular row order.

If you need the last 10 records according to an ID, explicitly specify:

ORDER BY id DESC
LIMIT 10;

If you need the latest 10 records according to a timestamp:

ORDER BY created_at DESC
LIMIT 10;

The definition of “last” should always be tied to an explicit ordering column.

Common Mistake: Assuming ID Means Latest

An auto-incrementing ID often increases as records are inserted, but it should not automatically be treated as a timestamp.

For example, an application could insert:

id = 100
created_at = 2026-08-17 10:00:00

and later insert:

id = 101
created_at = 2026-08-16 10:00:00

In that situation, the highest ID is not the most recent timestamp.

If your definition of “latest” is based on time, use:

ORDER BY created_at DESC

rather than:

ORDER BY id DESC

Performance Considerations

For large tables, retrieving the last N rows efficiently depends heavily on indexes.

If you frequently run:

SELECT *
FROM athletes
ORDER BY id DESC
LIMIT 10;

and id is a primary key, MySQL can efficiently use the primary-key index.

For timestamp-based queries:

SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10;

an index on order_date may improve performance:

CREATE INDEX idx_orders_order_date
ON orders(order_date);

If you frequently use both a timestamp and an ID as a tie-breaker, a composite index may be appropriate:

CREATE INDEX idx_orders_date_id
ON orders(order_date, order_id);

The exact index design should be based on your workload and query execution plan.

You can inspect a query plan using:

EXPLAIN
SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10;

Complete MySQL Example

Here is a complete example you can copy and run:

CREATE TABLE athletes (
    id INT PRIMARY KEY,
    team VARCHAR(50) NOT NULL,
    points INT NOT NULL
);

INSERT INTO athletes VALUES
(1, 'Mavs', 22),
(2, 'Mavs', 14),
(3, 'Lakers', 37),
(4, 'Knicks', 19),
(5, 'Warriors', 26),
(6, 'Knicks', 40),
(7, 'Lakers', 21),
(8, 'Celtics', 15),
(9, 'Hawks', 18),
(10, 'Celtics', 23),
(11, 'Jazz', 25),
(12, 'Jazz', 18),
(13, 'Kings', 14);

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 10
) AS temp
ORDER BY id ASC;

The result is:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  4 | Knicks   |     19 |
|  5 | Warriors |     26 |
|  6 | Knicks   |     40 |
|  7 | Lakers   |     21 |
|  8 | Celtics  |     15 |
|  9 | Hawks    |     18 |
| 10 | Celtics  |     23 |
| 11 | Jazz     |     25 |
| 12 | Jazz     |     18 |
| 13 | Kings    |     14 |
+----+----------+--------+

To select the last three rows, simply change LIMIT 10 to LIMIT 3:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 3
) AS temp
ORDER BY id ASC;

The output is:

+----+-------+--------+
| id | team  | points |
+----+-------+--------+
| 11 | Jazz  |     25 |
| 12 | Jazz  |     18 |
| 13 | Kings |     14 |
+----+-------+--------+

Frequently Asked Questions

How do I select the last 10 rows in MySQL?

Use:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 10
) AS temp
ORDER BY id ASC;

How do I select the last 5 rows?

Change the LIMIT value:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 5
) AS temp
ORDER BY id ASC;

How do I select the last 3 rows?

Use:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 3
) AS temp
ORDER BY id ASC;

How do I select the last row in MySQL?

If the highest id represents the latest record:

SELECT *
FROM athletes
ORDER BY id DESC
LIMIT 1;

How do I select the latest 10 records by date?

Use the date or timestamp column:

SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10;

If you want those 10 records displayed chronologically:

SELECT *
FROM (
    SELECT *
    FROM orders
    ORDER BY order_date DESC
    LIMIT 10
) AS recent_orders
ORDER BY order_date ASC;

Why do I need a subquery?

You need the subquery when you want to first identify the last N rows using descending order and then display only those rows in ascending order.

Without the subquery:

SELECT *
FROM athletes
ORDER BY id DESC
LIMIT 10;

the selected rows remain in descending order.

Does LIMIT guarantee the last rows?

Not by itself. LIMIT only limits the number of rows returned. To define which rows are “last,” you should use an appropriate ORDER BY column.

Conclusion

Selecting the last N rows in MySQL is straightforward when you combine ORDER BY and LIMIT.

To select the last 10 rows based on an id column and display them in ascending order, use:

SELECT *
FROM (
    SELECT *
    FROM athletes
    ORDER BY id DESC
    LIMIT 10
) AS temp
ORDER BY id ASC;

The inner query identifies the last 10 records:

SELECT *
FROM athletes
ORDER BY id DESC
LIMIT 10;

The outer query then sorts those selected records in ascending order.

You can change LIMIT 10 to any number you need, such as 3, 5, 20, or 100.

For production applications, however, make sure that the column used in ORDER BY actually represents what you mean by “last.” If you need the most recent records, a created_at, order_date, transaction_time, or similar timestamp is often more appropriate than relying solely on an ID.

When working with large tables, appropriate indexes and EXPLAIN can also help ensure that last-N queries remain efficient.

Tags:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

5 × 1 =