query

Question

query

srik780

Mr or Mrs. 500

Points: 585
More actions
April 19, 2010 at 9:49 pm

#231872

How to retrive nth record from table?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

use the row_number() function in an inner query, and have an outer query have the alias'ed columname for rownumber e the specific value:

select * from

(

SELECT row_number() over (order by name)AS RW, * from sysobjects

)

WHERE RW=43

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul White SSC Guru Points: 150457 More actions · Answer 2

Also:

-- Method A

SELECT TOP (1) *

FROM (

SELECT TOP (43) *

FROM sys.objects

ORDER BY [object_id] ASC

) SO

ORDER BY SO.[object_id] DESC;

-- Method B (a bit dodgy)

DECLARE @pk INTEGER;

SELECT TOP (43)

@pk = [object_id]

FROM sys.objects

ORDER BY [object_id] ASC;

SELECT *

FROM sys.objects

WHERE [object_id] = @pk;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

dhlotter SSC Veteran Points: 268 More actions · Answer 3

The from subquery just need to get aliased as well.

SELECT *

FROM (SELECT ROW_NUMBER() OVER (ORDER BY [NAME]) AS 'RW', * FROM [MASTER].[DBO].[SYSOBJECTS]) S

WHERE S.[RW] = 43

srik780 Mr or Mrs. 500 Points: 585 More actions · Answer 4

thank u very much

and In your answer what is 'SO' means what is the use of that?

Paul White SSC Guru Points: 150457 More actions · Answer 5

srik780 (4/20/2010)
thank u very much and In your answer what is 'SO' means what is the use of that?

It is the name I gave to the derived table. A derived table has to have a name - I chose SO to stand for sys.objects.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi