SQL Server – Select Nth Row

Seems like I’m now stuck with .NET development and the current tasks where developing SQL scripts. One of the spec was to return the first record, second record and third record as columns for a row. I know how to extract the first row, but second, third and so on is something that I still need to search.

Overview

The goal was to select several rows with multiple outer joins. Some columns are coming from suppose-to-be joins but are instead displayed as columns. For example, in a join, if the join produce at least 3 records on the other side of the join, the row will be repeated 3 times. But instead, what they want is to display only a single row and put those at 3 records as columns of the final result set.

Where Price 1, Price 2 and Price 3 are suppose to be joins and maximum expected rows are 3. But instead they are displayed as a single row and prices become columns. If this was MySQL then it would be easier because MySQL has LIMIT clause. Standard SQL does not have that beast so I’ll resort to using SQL Server’s own way.

Breakdown

In my task, there are six (6) columns for each joins and since it is required to have the first 3 records of the join, that is equivalent to (6*3) 18 additional rows aside from the main table. So let’s break them down into pieces.

First, let’s get the first record. I will not post the whole SQL query but focus in how to get the nth record instead for the purpose of this article.

Getting the first row (or second row or third row) requires that it is known in advanced how the records are sorted. There must be a sorting key of whatsoever.

select top 1
	inner_t13.vehicle_type
from
	処分業者契約マスタ inner_t12
	left outer join 処分契約委託運搬業者マスタ inner_t13 on inner_t12.contract_no = inner_t13.contract_no
order by
	inner_t13.carrier_cd2 ASC

Forget about the Japanese characters. top 1 will restrict the result into 1 record and since there is an order by clause, the first record in that order will re returned.

How about returning the second record? One can suggest the if we will select top 2 records sorted in reverse and let the outer query select the top 1 record, it can retrieve the second record easily. Take a look at this.

select top 1
	price
from
	(select top 2
		price,
		effective_date
	from
		item_list
	order by
		effective_date DESC
	) derived_table
order by
	derived_table.effective_date ASC

See? That would return the second record according to theory. However, if there is only one record in that condition, the first record will be returned instead which is wrong!

The correct way was to use the ROW_NUMBER(). Found that from the internetz (is made of catz).

select
	price
from
	(select top 2
		ROW_NUMBER() OVER (ORDER BY effective_date ASC) as row_no,
		price
	from
		item_list
	order by
		effective_date ASC
	) derived_table
where
	derived_table.row_no = 2

For returning the third record, simply replace top 2 with 3 and derived_table.row_no = 2 with 3. Since I have now gathered the bits and pieces of the solution, I’m now ready to unleash the scripts.

For returning the second record, here is my script.

select
	transport_price
from
	(select top 2
		ROW_NUMBER() OVER (ORDER BY inner_t13.carrier_cd2 ASC) as row_no,
		inner_t13.transport_price
	from
		処分業者契約マスタ inner_t12
		left outer join 処分契約委託運搬業者マスタ inner_t13 on inner_t12.contract_no = inner_t13.contract_no
	order by
		inner_t13.carrier_cd2 ASC
	) derived_inner_t13
where
	derived_inner_t13.row_no = 2

and for returning the third record:

select
	transport_price
from
	(select top 3
		ROW_NUMBER() OVER (ORDER BY inner_t13.carrier_cd2 ASC) as row_no,
		inner_t13.transport_price
	from
		処分業者契約マスタ inner_t12
		left outer join 処分契約委託運搬業者マスタ inner_t13 on inner_t12.contract_no = inner_t13.contract_no
	order by
		inner_t13.carrier_cd2 ASC
	) derived_inner_t13
where
	derived_inner_t13.row_no = 3

What about putting those records as columns in the base query?

Well, it is actually a top secret project that I cannot expose such huge SQL scripts. Anyway, there is nothing special to it, it is just a sub-query on select.