Languages
[Edit]
EN

MySQL - select last N rows

0 points
Created by:
Lia-Perez
505

In this article, we would like to show you how to select last N rows from a table in MySQL.

Quick solution

Ascending order:

SELECT * FROM (
    SELECT * FROM `table_name` 
	 ORDER BY `column_name` DESC
	 LIMIT N
) subquery
ORDER BY `column_name` ASC;

Descending order:

SELECT * FROM `table_name` 
ORDER BY `column_name` DESC 
LIMIT N;

Practical example

To show how to select last N rows from a table, we will use the following table:

MySQL - example data used to select last N rows from a table
MySQL - example data used to select last N rows from a table

Note:

At the end of this article you can find database preparation SQL queries.

Example - ascending order

In this example, we will select last three users from the users table in ascending order.

Query:

SELECT * FROM (
    SELECT * FROM `users` 
	 ORDER BY `id` DESC
	 LIMIT 3
) subquery
ORDER BY `id` ASC;

Output:

MySQL - select last N rows in ascending order result
MySQL - select last N rows in ascending order result

Example - descending order

In this example, we will select last three users from the users table in descending order.

Query:

SELECT * FROM `users` ORDER BY `id` DESC LIMIT 3;

Output:

MySQL - select last N rows in descending order result
MySQL - select last N rows in descending order result

Database preparation

create_tables.sql file:

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`country` VARCHAR(15) NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

insert_data.sql file:

INSERT INTO `users`
	(`name`, `country`)
VALUES
	('Tom', 'Poland'),
	('Chris', 'Spain'),
	('Jack', 'Spain'),
   ('Kim', 'Vietnam'),
   ('Marco', 'Italy'),
	('Kate', 'Spain'),
	('Nam', 'Vietnam');

Alternative titles

  1. MySQL - select last N rows from a table
  2. MySQL - select last N rows in ascending order
  3. MySQL - select last N rows in descending order
Donate to Dirask
Our content is created by volunteers - like Wikipedia. If you think, the things we do are good, donate us. Thanks!
Join to our subscribers to be up to date with content, news and offers.

MySQL

Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join