how to retrieve a row by specifying row number

Hi,
Is there any sql command to retrieve a particular row by specifying its
row number."supriya" <santu@.hotmail.com> wrote in message
news:OriSXyJTGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any sql command to retrieve a particular row by specifying its
> row number.
Oh I can't wait for the replies to this one :-)

>|||hi
you can use this type of Query
This Query is from Pubs DB
SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2
WHERE e2.lname <= e.lname AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY lname
Hope this helps you
Regards
Shrinivas|||Hi
What vesrion are you using?
SQL Server 2005
CREATE TABLE SpeakerStats
(
speaker VARCHAR(10) NOT NULL PRIMARY KEY,
track VARCHAR(10) NOT NULL,
score INT NOT NULL,
pctfilledevals INT NOT NULL,
numsessions INT NOT NULL
)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron', 'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy', 'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe', 'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert', 'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike', 'DB', 8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian', 'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin', 'DB', 7, 25, 4)
--1 (ROW_NUMBER())
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
speaker, track, score
FROM SpeakerStats
ORDER BY score DESC
--2
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, speaker
--3
DECLARE @.pagenum AS INT, @.pagesize AS INT
SET @.pagenum = 2
SET @.pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,
speaker, track, score
FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@.pagenum-1)*@.pagesize+1 AND @.pagenum*@.pagesize
ORDER BY score DESC, speaker
"supriya" <santu@.hotmail.com> wrote in message
news:OriSXyJTGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any sql command to retrieve a particular row by specifying its
> row number.
>