-- Shakespeare, Henry IV Part 2
Act IV, Scene V, Line 182
Intro
Say you have the following need.
Given the following trans table:
CREATE TABLE trans (
id int identity(1,1) not null
,transactionId int not null
,transactionTime datetime2 not null default (sysutcdatetime())
,amount int not null
);
And the following data:
INSERT INTO trans (transactionId, amount) VALUES(1, 100);
INSERT INTO trans (transactionId, amount) VALUES(1, 125);
INSERT INTO trans (transactionId, amount) VALUES(2, 400);
INSERT INTO trans (transactionId, amount) VALUES(4, 25);
INSERT INTO trans (transactionId, amount) VALUES(5, 20);
INSERT INTO trans (transactionId, amount) VALUES(6, 70);
INSERT INTO trans (transactionId, amount) VALUES(7, 100);
INSERT INTO trans (transactionId, amount) VALUES(8, 33);
** Note, done this way to have different transactionTime **
When I query the trans table
Then I want to get the last 5 records in the trans table as indicated by transactionTime
This setup will give us the following.
Luckily, SQL Server 2012 offers a few ways to look at the last few records of a table.
TOP N
SELECT TOP 5 *
FROM trans
ORDER BY transactionTime DESC
;
By ordering by the transactionTime in descending order we are able to use the TOP filter to obtain the last 5 records.
ORDER BY with FETCH and OFFSET
SELECT *
FROM trans
ORDER BY transactionTime DESC
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY
;
Using the ORDER BY clause, we can use the new to SQL Server 2012 FETCH and OFFSET we are able to follow the ISO SQL 2008 standard for our limiting.
Page Size option
DECLARE @PageSize INT = 5
SELECT *
FROM trans
ORDER BY transactionTime DESC
OFFSET 0 ROWS
FETCH NEXT (SELECT @PageSize) ROWS ONLY
;
In SQL Server 2012, the new FETCH on the ORDER BY clause allows for queries to obtain the number of rows to retrieve!
Using ROW_NUMBER
SELECT id, transactionId, transactionTime, amount
FROM (
SELECT
ROW_NUMBER() OVER(ORDER BY transactionTime DESC) AS row
,*
FROM trans
) AS r
WHERE r.row <= 5
;
Using the ROW_NUMBER() windowing function, we can create a poor man's FETCH and OFFSET by filtering the row numbers returned to be the number of records we want to obtain.
This idea came from Suprotim Agarwal's blog post on SQL Server Curry.
Performance
"Inform the database whenever you don't need all rows."
-- Markus Winand, Use the Index Luke - Top-N Rows Queries
SQL Server offers many different ways to get the last 5 records, but one must think of performance. If you have an indexed order by then the DBMS would not even have to perform the sort!
