SQL query to get last 3 rows (records) in a Table in SQL Server

There a few simple methods (or SQL queries) using which you can get or select the last 3 rows or records in a table in SQL Server. I am sharing 3 different methods here and all are useful in different scenarios, especially the last method.

Method 1

Assuming I have a table named dbo.birds, which has a column named ID. Its of type int and its a Primary key column. Therefore, all the IDs are unique.

Note: Please see the dbo.birds table, as I’ll be using this table in the first 2 methods.

Now, the query to get the last 3 rows,

SELECT TOP 3 *FROM dbo.Birds ORDER BY ID DESC

This one-liner is the simplest query in the list, to get the last 3 number of records in a table. The TOP clause in SQL Server returns the first N number of records or rows from a table. Applying the ORDER BY clause with DESC, will return rows in descending order. Hence, we get the last 3 rows.

image

Showing last 3 rows or records in a table in SQL Server

Method 2

Here’s a method that I offen use, for the reason, it does not alter (or reverse) the sequence. See the sequence of IDs in the first method.

The SQL query...

SELECT *FROM dbo.Birds b1
WHERE 3 > (
    SELECT COUNT(*) FROM dbo.Birds b2
        WHERE b2.ID > b1.ID
)

image

select last 3 rows or records in a table

You can howerver, use the ORDER BY b1.ID DESC to reverse the sequence (if you want).

Method 3 (using ROW_NUMBER() function)

This method is also important and interesting too. Now, let us assume I have a table that does not have a primary key or an ID column. How do I query and get the last 3 rows?

Here's a table.

CREATE TABLE [dbo].[ColorMaster](
    [ColorName] [varchar](20) NOT NULL,
    [HexCode] [varchar](10) NOT NULL
)

Just 2 columns, ColorName and HexCode. Columns are of type varchar. Now, insert few rows in it.

image

Table in SQL Server with color hex code

Since the table does not have a column of type int, it may not show a desired result. In such case, I’ll have to create row ids for each row dynamically (using a query of course). Ok, here’s how it is done.

SELECT TOP 3 *, ROW_NUMBER() OVER 
(
    ORDER BY CAST(GETDATE() AS TIMESTAMP)
) RowNumber
FROM dbo.ColorMaster ORDER BY RowNumber DESC

image

Showing last 3 records in SQL Server table using ROW_NUMBER()

I am using ROW_NUMBER() function to create unique row numbers for each row. See the ORDER BY clause inside OVER(). I cannot use the column ColorName, as it will order alphabetically. Therefore, I am using GETDATE() function to get the date and time when these rows were inserted and accordingly getting the last 3 rows.

← PreviousNext →