Languages
[Edit]
EN

MS SQL Server - select last N rows

0 points
Created by:
Elleanor-Williamson
320

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

Quick solution

Ascending order:

SELECT * FROM (
     SELECT TOP N * 
     FROM [table_name] 
	 ORDER BY [column_name] DESC
) subquery
ORDER BY [column_name] ASC;

Descending order:

SELECT TOP N * 
FROM [table_name] 
ORDER BY [column_name] DESC;

Practical example

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

MS SQL Server - example data used to select last N rows from a table
MS SQL Server - 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 the last three users from the users table in ascending order.

Query:

SELECT * FROM (
     SELECT TOP 3 * 
	 FROM [users] 
	 ORDER BY [id] DESC
) subquery
ORDER BY [id] ASC;

Output:

MS SQL Server - select last N rows in ascending order result
MS SQL Server - select last N rows in ascending order result

Example - descending order

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

Query:

SELECT * FROM [users] ORDER BY [id] DESC LIMIT 3;

Output:

MS SQL Server - select last N rows in descending order result
MS SQL Server - select last N rows in descending order result

Database preparation

create_tables.sql file:

CREATE TABLE [users] (
	[id] INT IDENTITY(1,1),
	[name] VARCHAR(100) NOT NULL,
	[country] VARCHAR(15) NOT NULL,
	PRIMARY KEY ([id])
);

insert_data.sql file:

INSERT INTO [users]
	([name], [country])
VALUES
	('Tom', 'Poland'),
	('Chris', 'Spain'),
	('Jack', 'Spain'),
    ('Kim', 'Vietnam'),
    ('Marco', 'Italy'),
	('Kate', 'Spain'),
	('Nam', 'Vietnam');
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.

MS SQL Server - problems

MS SQL Server - select last N rows
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