Select row number N

1kHz · December 23rd, 2004, 09:44 PM

Hi all,
How do we select the n'th row from a table?
Lets say I want to pick the 7th row, how should i do it? I already have an IDENTITY column, but I can't select based on it since the number might be skipped by delete statements..

I'm using Ms SQL 2000..

jemacc · December 23rd, 2004, 10:21 PM

you can this something like this

Code:

SELECT TOP 7 *
FROM
SELECT TOP 500 *
FROM myTable
ORDER BY id) AS TopOfTable
ORDER BY id DESC

Jaime E. Maccou

1kHz · December 26th, 2004, 09:20 PM

Thanks for the reply jemacc..

I've change the SQL to this:

Code:

SELECT TOP 1 *
FROM
(SELECT TOP 7 *
FROM myTable
ORDER BY id) AS TopOfTable
ORDER BY id DESC

but i want the number 'N' to be a variable, but this code wont work:

Code:

DECLARE @wanted int
SET @wanted = 7

SELECT TOP 1 *
FROM
(SELECT TOP @wanted *
FROM myTable
ORDER BY id) AS TopOfTable
ORDER BY id DESC

It says "Incorrect syntax near '@wanted'". The number for TOP can't be a variable?

jemacc · December 27th, 2004, 01:02 AM

Code:

CREATE PROCEDURE TOP_TEST
(@topn char(6))  
AS 
declare @sqlstring  nvarchar(2000) 
declare @parmstring  nvarchar(2000) 

set @sqlstring='select top '    
    +@topn
    +' * from MYTABLE' 
set @parmstring='@topn varchar(20)' 
exec sp_executesql @sqlstring,@parmstring,' @topn = @topn' 

exec test 7

Jaime E. Maccou

1kHz · December 27th, 2004, 06:22 AM

wow, first time seeing exec sp_executesql..
thanks jemacc