How can i get the 2nd, 3rd or N''th row from a table?

This would be a TOP Clause question.

if we use TOP clause for example;

SELECT Top(4) Col_A, Col_B

FROM Table_A

ORDER BY Col_A

This query gets the first 4 rows according to the order of Col_A.

How can i get only the second or the third row from this table?

Thanks for your helps...

--2
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 1 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]

--3
select top 1 categoryID from dbo.Categories
Where categoryID not in(select top 2 categoryID from dbo.Categories order by [categoryName])
order by [categoryName]|||

Hi phokaia,

you can try the sample code as below:

--Start : Create sample db,table,data --

Create Database d1
go

use d1
go

Create table t1
(c1 int ,
c2 char(10)
)
go

insert t1 values (1,'a')
insert t1 values (2,'a')
insert t1 values (3,'a')
insert t1 values (4,'a')
go

-- In SQL2K, you can try this

declare @.i int
declare @.c char(10)

declare cur_top cursor SCROLL for select * from t1 order by c1
open cur_top;

Fetch ABSOLUTE 3 from cur_top into @.i,@.c
close cur_top;
deallocate cur_top;

select @.i,@.c

-- OR --

In SQL2K05, you can try this , if you need the 3rd row.

Select *
from (select *,'RowNum'=ROW_NUMBER() OVER (ORDER BY c1)
　　　from t1) temptable
where RowNum=3

try it

hoping this can help you.

Best Regrads,

Hunt.