in SQL Server

Selecting a row position within a SELECT query

I want to get the third row in a result set from the following:

ID   Name

1   A

2  B

3  C

4 D

You can acheive this by using the ROW_NUMBER() rank function using SQL Server 2005/2008.   The following code will return the 3rd record.

create table #temp
(
ID int,
[Name] nvarchar(50)
)

insert into #temp values (1,’A’)
insert into #temp values (2,’B’)
insert into #temp values (3,’C’)
insert into #temp values (4,’D’)
insert into #temp values (5,’E’)

select [Name] from #temp where ID in (select ID from
(select ID, ROW_NUMBER() OVER (order by ID asc) as [Row] from #temp) as subquery
where [Row] = 3)

drop table #temp

Result:

Name = C