looking for nth largest number for column in row(s

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)

looking for nth largest number for column in row(s

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2010-11-29 : 00:29:41

Hello,

I am trying to write a query that returns the nth largest number for a column across all rows in a table and returns the ID(s), too. The query I'm using works well, but there is an oversight which is it only returns one result.



select top 1 ID, number from table where number in (select top n number from table order by number asc) order by number desc

The oversight is what I listed in the first sentence, that there may more than one ID with the same number. So, this query I have won't work for what I need in this example:


Table
ID       Number
1        10
2        50
3        0
4        100
5        7
6        10

If I want the 3rd lowest number, for example, then the query should return:


ID    Number
1     10
6     10

How may i accomplish this, either with a new query or a modification of mine?

Thank you.

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2010-11-29 : 00:49:11

nevermind, I just found an excellent link that i saw in another thread. Thanks.

http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-29 : 02:19:37

declare @table table
(
id tinyint,
number int
)

insert into @table
values(1,10),(2,50),(3,0),(4,100),(5,7),(6,10),(7,100)

select * from @table
order by number
select * from @table t
where 2=( select COUNT(number) from @table t1
where t.number >t1.number
)
order by t.number

This may work.

Iam a slow walker but i never walk back

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-29 : 05:17:14

Other methods
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources