Fun ROWNUM() case, get n’th row from dataset.

Last week, one of my coworkers had a nice case. From a dataset, he needed the third row based on an indicator and a timestamp. That sounded a bit vague so I started out with creating a table that contained some records with the columns he’d have to filter on.

CREATE TABLE dbo.demo
(
	id INT IDENTITY(1,1),
	speccode INT,
	datum DATETIME,
	ind INT
)

Let’s add some data to this table, just to make sure any query will return data for this case

INSERT INTO dbo.demo 
 (speccode, datum, ind )
 VALUES
(1, DATEADD(m,1,SYSDATETIME()),1),
(1, DATEADD(m,2,SYSDATETIME()),1),
(1, DATEADD(m,3,SYSDATETIME()),1),
(2, DATEADD(m,1,SYSDATETIME()),1),
(2, DATEADD(m,2,SYSDATETIME()),1),
(2, DATEADD(m,3,SYSDATETIME()),1),
(2, DATEADD(m,4,SYSDATETIME()),1),
(2, DATEADD(DAY,1,SYSDATETIME()),1)

My first suggestion was to use the TOP and OFFSET function. Something like this

SELECT  id,
		speccode,
		datum,
		ind
FROM dbo.demo
ORDER BY datum ASC
OFFSET 3 rows FETCH NEXT 1 ROWS ONLY

This little query did the trick, but then things changed. Because the column speccode needed to be included; for every speccode the third row needed to be returned.

The highlighted rows are the ones that needed to be returned to the user. Maybe you could achieve it with the OFFSET function, but I went into another direction. Because there’s a clear, stable filter and it’s always the third row that has to be returned, I can use the ROWNUMBER() function. This function has a PARTITION BY clause built-in so I can tell it to start counting rows for each speccode.

SELECT id,
	speccode,
	datum,
	ind,
	ROW_NUMBER() OVER (PARTITION BY speccode ORDER BY datum ASC) AS rnum
FROM dbo.demo

This query has the following result:

You can see that for speccode 1, there are three rows and for speccode 2 there are 5. Nice, now I need to filter on them, because I only want the ones with rnum = 3. First I tried just adding a HAVING clause, but it returns an error:

So I went with my preferred way of working, Common Table Expressions. I like them because it keeps code tidy, readable and controllable.

WITH prep as (
SELECT id,
	speccode,
	datum,
	ind,
	ROW_NUMBER() OVER (PARTITION BY speccode ORDER BY datum ASC) AS rnum
FROM dbo.demo
)
SELECT id,
		speccode,
		datum,
		ind,
		rnum
from prep
where rnum = 3

The ‘prep’ CTE prepares the result as I’ve shown in the earlier screenshot, the final select just pulls out the records where the rnum value equals 3.

This solved the issue for my coworker and he could happily continue with more interesting business logic.

For those of you interested, here’s the query plan:

Of course, the real dataset was much larger (rows and columns) but the concept still stands.

Thanks for reading!