Hi all,
when you are selecting the data from a table and you are looking for the topmost or top N rows, getting that is easy with the TOP clause. But, for getting the Nth row in the table there is no direct method. But there are a couple of workarounds to it, that I am giving here.
The first is the old man's method who likes using the normal SQL query capabilities. (Could be a bit inefficient). Here is a method to get the 18th row from the table Sales.SalesOrderDetail, which you can find in the famous AdventureWorks database. If you dont have it, either download it, or change the query to use your table and column names.
Select Top 1 LineTotal FROM
(Select Top 1 LineTotal FROM
(Select Top 18 LineTotal From Sales.SalesOrderDetail ORDER BY LineTotal Desc) AS a
Order By LineTotal) as b
The other method is using the Row_Number() function, which assigns the sequential numbering to the rows on the basis of the Order By clause. We have used the CTE here because the Ranking functions can not be used in the where clause. Have a look.
WITH MyCTE
AS
(
SELECT DepartmentID, Name, ROW_NUMBER() OVER(ORDER BY DepartmentID) AS MyRank
FROM HumanRescources.Department
)
SELECT * FROM MyCTE
WHERE MyRank = 18
I hope that you find the post useful.
