SELECT a row by number in ordered table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT a row by number in ordered table

Author

Topic

Russ1984
Starting Member

4 Posts

Posted - 2008-11-10 : 10:48:05

Hi,

I'm trying to select a certain row number in an orderded table, i.e. the third row as it appears in the given order, also the number is a parameter. A way what I want to be achieved is:

SELECT @valueOfRankIntegerPart = speed FROM Speed WHERE speed IN
(
SELECT TOP(@rankIntegerPart) speed FROM Speed ORDER BY speed ASC
)
ORDER BY speed ASC

But I really want to do this without nesting and effectively selecting from the Speed table twice. Is there a better way to achieve this result?

Thanks in advance.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-11-10 : 10:53:59

Yes. Use

ROW_NUMBER() OVER (ORDER BY speed).

Russ1984
Starting Member

4 Posts

Posted - 2008-11-10 : 11:06:19

You appear to misunderstand. Perhaps I should have said '...select a row of certain number...' I don't want the row number, I have that in a variable. I want to return the data at row number '@rankIntegerPart' when the data is ordered by speed.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-11-10 : 11:22:11

he was suggesting using something like:

select @valueOfRankIntegerPart = speed from (
select row_number() over(order by speed) as rn, * from speed ) a
where rn = @rankIntegerPart

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-11-10 : 11:23:10

[code]SELECT @valueOfRankIntegerPart = speed
FROM Speed
WHERE ROW_NUMBER() OVER (ORDER BY speed) = @rankIntegerPart[/code]

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-11-10 : 11:25:07

Won't work... use jdaman's solution.

Russ1984
Starting Member

4 Posts

Posted - 2008-11-11 : 03:51:09

Thanks a lot guys, that sped up my query by almost 25%.