| N th highest column value [message #371257] |
Sun, 24 September 2000 04:46  |
Vineet
Messages: 10
Registered: September 2000
|
Junior Member |
|
|
Hi,
I need to fetch the Nth highest value of the column in the table. e.g. I want to fetch the employee's name whose salary is the fourth higthest in the deparment.
Regards
Vineet
|
|
|
|
| Re: N th highest column value [message #371263 is a reply to message #371257] |
Mon, 25 September 2000 04:56  |
Letchoumy
Messages: 5
Registered: September 2000
|
Junior Member |
|
|
This query find the N th highest amount from the table
If you have a table salary(name,amount,...)
This query give you the Nth highest amount. You can change this query according to your needs.
select min(amount) from
(select tt.amount from (select amount from salary group by amount) tt
minus
select tt.amount from (select amount from salary group by amount) tt where
rownum < (select count(distinct amount)-3 from salary))
The number 3 indicates (4th - 1). So if you want 10th highest put 9 instead of 3.
I hope that it may be helpful to you.
regards
|
|
|
|
| Re: N th highest column value [message #371271 is a reply to message #371257] |
Tue, 26 September 2000 05:10 |
Thavamani
Messages: 11
Registered: July 2000
|
Junior Member |
|
|
Hi,
If you have a table with columns name,sal and you want 4th highest salary.
select name,sal
from table t
where 4=(select count(distinct sal)
from table t1
where t1.sal>t.sal)
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
