can one Retrieve only the Nth row from a table?

Thanks in advance

MySQL

create table test_nthrow
( id integer
, name varchar(100)
);
insert into test_nthrow values (1,'row 1');
insert into test_nthrow values (2,'row 2');
insert into test_nthrow values (3,'row 3');
insert into test_nthrow values (4,'row 4');
insert into test_nthrow values (5,'row 5');

to get nth row use (n-1) in LIMIT condition since mysql rownumber starting from 0
to get 5th row
select * from test_nthrow limit 4,1

Oracle
In oracle you may use this query to get 5th row
select * from (select ROWNUM rn,t.* FROM test_nthrow t) WHERE rn=5

This is just one way getting Nth row, you can use alternate method also in this case .

__________________
Keep smiling...

Hi Priyan

it is very useful for me. but i need query for sql server 2005

Hi Santha,

I think this will help you ,

Begin
With ProjectTable As
(Select *, Row_Number()Over(Order By ProjectID) as RowNum from Project)
Select * from ProjectTable Where RowNum = 9
End

Here the 'ProjectTable' is the Common Table Expression used to hold the result set of the project table temporarily.
You can use any table instead of project table.

Tell me How usefull was it

Thanks,
L.Srikumar

Hi Santha,

You can try this also,

Select *
From (Select *,(Row_Number() Over (Order By ProjectID)) As Rownum
From Project) P
Where P.Rownum = 10

Hi

This query is very useful for me . its working fine in sql server . can u please explain this query.?

Thank u

is really useful for me, I am glad to read it here.