How to Get nth Record in a Table?

 Posted by Lakhangarg on 9/8/2009 | Category: Sql Server Interview questions | Views: 7072
Answer:

First Get the n records fron the table using

Select Top n FROM UserTable
Now Reverse the Order using identity Column like:
Select Top n FROM UserTable Order By 1 DESC

Now we need nth record that can be get as
SELECT TOP 1 * FROM (Select Top n FROM UserTable Order By 1 DESC)AC

For Example i need to get 5th record From userTable then quey will be like this:
SELECT TOP 1 * FROM (SELECT TOP 5 * FROM UserTable Order By 1 DESC)AC


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Pandians on: 9/8/2009
Hi

Insteadof Using TOP/SUB-QUERY We can use DENSE_RANK() function in SQL SERVER 2005/2008.
;WITH CTEs

AS
(SELECT DENSE_RANK() OVER(ORDER BY COL1 DESC) 'Nth',* FROM TABLE1)

SELECT * FROM CTEs WHERE NTH = 5


Cheers
Posted by: Keneo on: 9/26/2009
The above SQL statements(TOP & CTE) will fetch the "N"th record from the bottom and not the "N" th record.

Following is the modification to the satements:

SELECT TOP 1 * FROM (SELECT TOP 5 * FROM UserTable Order By 1 ASC) TEMP Order By 1 DESC


;WITH CTEs

AS
(SELECT DENSE_RANK() OVER(ORDER BY COL1 ASC) 'Nth',* FROM TABLE1)
SELECT * FROM CTEs WHERE NTH = 5


Confirm the changes..

Login to post response