CODEWITHSUNDEEP
sql-server-2008
sam
sam

Reputation: 1304

T-Sql for getting the fifth row from a table

I found a very strange thing while extracting the 5th row from the table I used the below mentioned query and thought it would be possible by first taking the top 5 rows and then in the outer query take the top 1 after ordering in descending order. But the below query is giving wrong output.

select top 1 * 
from 
     (select * 
      from 
          (select top 5 BusinessEntityID, FirstName, LastName 
           from person.person) as a 
     ) as b
order by 
    b.BusinessEntityID desc

This query is giving me the last row and not the 5th row. I know this can be easily achieved with row_number() function but I want to know why the above query is giving the wrong output.

Upvotes: 0

Views: 45

Answers (1)

sam yi
sam yi

Reputation: 4934

I believe you're missing the ORDER BY in the TOP 5 query. And you can remove one of the SELECT *

select top 1 * 
from (
    select top 5 BusinessEntityID,FirstName,LastName 
    from person.person
    order by BusinessEntityID asc
) as a
order by a.BusinessEntityID desc

Also, you are correct, this would be much simpler with ROW_NUMBER.

Upvotes: 3

Related Questions