Selecting a row position within a SELECT query
Posted by Tim on March 11, 2009 in SQL Server |
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
-
-
-
CodeLab Twitter- codelabnz: @possiblyb9 I now have blackberry for breakfast February 7, 2012
- codelabnz: I have now joined most ppl in indonesia and got myself a blackberry February 7, 2012
- codelabnz: From a bb February 6, 2012
- codelabnz: @bartekmarnane some info on micro orms http://t.co/UDfv5ML7 February 5, 2012
- codelabnz: @bartekmarnane depends on the project, but i found u have 2 be very careful with EF and the type of SQL it generates February 5, 2012
- codelabnz: @bartekmarnane I have been using EF for awhile, good for building apps quickly, but found it performs bad on higher traffic web applications February 5, 2012
- codelabnz: stuff it, I give up on Entity Framework, going to a micro ORM, back to writing SQL queries! I would rather do this than have a slow app! February 5, 2012
- codelabnz: Been too busy for tweeting lately January 21, 2012
- codelabnz: Blog Post: Setting the screen position when visiting previous page with jQuery http://t.co/evuKxVv0 January 17, 2012
- codelabnz: is anyone using Godaddy Cloud hosting? can someone provide their website so I could do a speed test? January 9, 2012