How can I select the last N rows from a MySQL table?

---

You can use the following syntax in MySQL to select the last N rows from a table:

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

This particular example selects the last 10 rows from the table named athletes and orders the results in ascending order by the values in the id column of the table.

To select a different number of rows, simply change the number after LIMIT in the query.

The following example shows how to use this syntax in practice.

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

Suppose we have the following table named athletes that contains information about various basketball players:

-- create table 
CREATE TABLE athletes (
  id INT PRIMARY KEY,
  team TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 14);
INSERT INTO athletes VALUES (0003, 'Lakers', 37);
INSERT INTO athletes VALUES (0004, 'Knicks', 19);
INSERT INTO athletes VALUES (0005, 'Warriors', 26);
INSERT INTO athletes VALUES (0006, 'Knicks', 40);
INSERT INTO athletes VALUES (0007, 'Lakers', 21);
INSERT INTO athletes VALUES (0008, 'Celtics', 15);
INSERT INTO athletes VALUES (0009, 'Hawks', 18);
INSERT INTO athletes VALUES (0010, 'Celtics', 23);
INSERT INTO athletes VALUES (0011, 'Jazz', 25);
INSERT INTO athletes VALUES (0012, 'Jazz', 18);
INSERT INTO athletes VALUES (0013, 'Kings', 14);

-- view all rows in table
SELECT * FROM athletes;

Output:

+----+----------+--------+
| 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 |
+----+----------+--------+

Notice that the table has a total of 13 rows.

Suppose that we would like to select the last 10 rows from the table.

We can use the following syntax to do so:

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

Output:

+----+----------+--------+
| 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 |
+----+----------+--------+

This query selects the last 10 rows of the table, in ascending order based on the values in the id column.

For example, we can use the following syntax to select the last 3 rows in the table:

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

Output:

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

Notice that only the last 3 rows in the table are selected.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL: