How to Get nth Record in a Table?

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

Post |

Interview FAQs |

Exclusive Questions |

Interview Home

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

Bookmark It

< Previous : If we remove a web.config file from the applicatio ...

Next > : How to get a row was inserted most recently in a t ...

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..

More Interview Questions by Lakhangarg

Latest Interview Questions