query

  • How to retrive nth record from table?

  • 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!

  • 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;

  • 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

  • thank u very much

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

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

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

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