How to get the n-th row in a SQL table ..in 4 different ways! Simple, clear and with example!

Hi guys,

After the deep previous post about the SQL statistic today we will talk about a more more easy topic.

Have you ever needed to extract from a table the second row, or the third or the n-th row?

This post is for you! 

 

Extract ..Get..Select... the n-th row in a table

We can solve this task in more than one way.

To follow my example you can simply execute the following command that create and fill the Ord table with some data

       
Create Table Ord (Id int identity(1,1) primary key, Code varchar(10))

Insert into Ord (Code) values ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I')

This is our table:

 

The first way I seen many times is using a derivate table.

 

Yes, you can solte the request by split the problem in two parts.

In the first part you can get the first n-th rows. This will be the derivate table.

 

Then you can get first row of this derivate table ordering by id in descending mode.

Is it not easy?

 

A simple example could be the following statement:

   
Select Top 1 * From 
  (Select top 3 * From ord Order By id) as t
order by id desc       
 

I get..

 

Another way to get the same result is to use the window functions.

We need a derivate table. 

The statement inside the derivate table use the Row_Number window function to show the number of row. The first row will have the row number 1, the second row the number  2 and so on.

 

We cannot look at the id field because some values may not be present, the table may not be sorted by the id field and also would not be logically correct.

You can extract the 3rd row in this way:

   
Select * From 
  (Select Row_Number() Over (Order by id) as numrow, * From Ord) As t
Where
  t.numrow = 3     
 

I get the same result

 

We have then the "Mathematical" way

We can get the nth row by using the division (/) operator. 

Since this operator perform a round to integer division operation, the following statement returns only those rows whose ID divided N and then rounded is 1.

For N = 3 i get the ID equal to 3,4,5 so i need to use also the TOP clause..

       
Select Top 1 * From Ord Where Id / 3 = 1

 

Finally we can get the result by using the OFFSET, FETCH NEXT clause. This clauses are defined in the SQL ISO ANSI standard.

       
Select * From Ord
Order By id
Offset 2 ROWS
Fetch NEXT 1 ROW ONLY

 

Great! We have 4 ways to get the nth row of a table.

....but what is the fastest way to get the nth row?

 

 

The fastest way to get the nth row

 Well we must say that the positive thing is that all four statements read the same number of pages (remember we have the primary key on the id field)

       

(1 row affected)
Table 'Ord'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

(1 row affected)
Table 'Ord'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

(1 row affected)
Table 'Ord'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

(1 row affected)
Table 'Ord'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

Completion time: 2023-01-08T19:27:00.5465354+01:00

  

Only the 3rd way (the statement that use the division operator) reads one more page.

Looking at the exection plans:

 

We can say that the worst performing statement is the first while the best is the fourth.

Use the Offset fetch next clause is a good way!

 

 

That's all for Today! Have a great week!

~Luke

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Presvious post: A proposal for a new way to manage SQL Statistics for data not evenly distributed. A dip in the logic of SQL Statistics!