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.
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 )
You can howerver, use the ORDER BY b1.ID DESC to reverse the sequence (if you want).
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.
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
Hope you find this examples useful. Thanks for reading.☺