Getting the third highest value in a column

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 2000 Forums

Transact-SQL (2000)

Getting the third highest value in a column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-12 : 07:46:23

nidhi writes "How do i get the third highest value in a particular column of a table ? for that matter, Nth highest value in that column ?"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-12 : 08:14:18

select top 1 val from (select top 3 val from tbl order by val desc) a order by val

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-12 : 10:28:41

Isn't that Backward?

select top 1 val from (select top 3 val from tbl order by val) a order by val desc

????

Of course it's not....[mutter]should know better by now[/mutter]

Always test before posting...

Brett

8-)

Lewie
Starting Member

42 Posts

Posted - 2004-01-13 : 02:34:31

create table #test
(val money)
insert into #test select 1
insert into #test select 3
insert into #test select 5
insert into #test select 80
insert into #test select 100
insert into #test select 105

Declare @Nth int

Select @Nth = 4

select b.val from #test a cross join #test b
where a.val <= b.val
group by b.val
having count(*) = @Nth

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-13 : 16:00:58

That won't work if your column is not unique...

create table #test
(val money)
insert into #test select 1
insert into #test select 3
insert into #test select 5
insert into #test select 100
insert into #test select 100
insert into #test select 105

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources