April 19, 2010 at 9:49 pm
How to retrive nth record from table?
April 19, 2010 at 11:11 pm
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
April 20, 2010 at 12:07 am
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
April 20, 2010 at 12:38 am
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
April 20, 2010 at 10:20 pm
thank u very much
and In your answer what is 'SO' means what is the use of that?
April 20, 2010 at 10:52 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply