RIDDLE

[hudi]

For a little entertainment!!

We all know how to find the highest value in a column (MAX(), SET ROWCOUNT 1 / ORDER BY, SELECT TOP 1)
But the question is: how to find the nth highest value in a column???

Well?? B-)

 

[bascy]

Wild guess:
get the rownumber as one of the result-columns, use order by desc and select the nth row

 

[foxdev]

I think this was answered in this forum a week or two ago. IIRC, you set the ROWCOUNT to be n, then select MIN() of the subselect SELECT...ORDER BY ColName. Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[tlbroadbent]

Check this thread - thread183-94199. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[agar]

You use a variation on a technique taught to me by foxdev (thanks again!!!

set nocount on
create table WHATEVER (COL int)
insert into WHATEVER (COL) values (3)
insert into WHATEVER (COL) values (5)
insert into WHATEVER (COL) values (87)
insert into WHATEVER (COL) values (255)
insert into WHATEVER (COL) values (5644)
insert into WHATEVER (COL) values (54)
insert into WHATEVER (COL) values (64)
insert into WHATEVER (COL) values (72)
insert into WHATEVER (COL) values (2)
insert into WHATEVER (COL) values (5)




declare @i int
declare @result int
set @i = 0

-- let's say you want to find the 6th highest value
select @i = @i + 1, @result = case @i when 6 then COL else @result end
 from WHATEVER
 order by COL desc
select @result

drop table whatever
set nocount off

the variable @result will have the value 54.

 

[agar]

p.s. the advantage to this is that if this is in a stored procedure, you can pass N as an argument -- whereas with the "select top" solution, N must be hardcoded. At least, this is true in 7.0, maybe not 2000.

J

 

[hudi]

Well guys you've tried, but only agar wrote the full anwser, which is incorrect in one little word!!

agar: you wrote desc, where it should be asc!!

We are queryinq for nth highest value!

Sorry, no chocholate for anyone!! LOL

 

[agar]

Don't take away the chocolate yet I tested the code and it works. Try it!

 

[hudi]

it doesn't return the nth highest value but it retuns nth minimal value!!

there's no chocholate for you!! :-9

bye

 

[agar]

?

In the set

(3, 5, 4, 6)

the number 6 is the highest value, the number 5 is the next highest value, and the number 4 is the third highest value -- right? So you order by desc, like this:

6
5
4
3

3 is the lowest value, 4 is the next lowest value, and 5 is the third lowest value, right? So if you wanted the third lowest value, you'd order by asc, like this:

3
4
5
6

Maybe I'm missing something -- not sure what "minimal value" is. (I knew I should have taken statistics...)

 

[hudi]

yeah sorry!! I just wake up now and now I can clearly see that I made a mistake when just quickly overviewed your solution which is correct!!

I was to sleepy yesterday to do something wright!

OK, you get a chocholate!! :-Q