OraFAQ Forum: Server Administration » Finding the Nth highest Salary value of given Employee Table

Home » RDBMS Server » Server Administration » Finding the Nth highest Salary value of given Employee Table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Finding the Nth highest Salary value of given Employee Table [message #372600]

Tue, 27 February 2001 05:56

S.Suresh Rajkumar
Messages: 1
Registered: February 2001

Junior Member

I Created one Employee Table name is EMP.
There is a column name is Salary.
I want to get the Nth higest Salary,Means the 3rd highest value.etc,. I want get that in one correlated query. Pls Help.

Report message to a moderator

Re: Finding the Nth highest Salary value of given Employee Table [message #372603 is a reply to message #372600]

Tue, 27 February 2001 07:42

Joachim Lindner
Messages: 30
Registered: February 2001

Member

This is a bit of tricky SQL but I may give you a clue on how to proceed ...

select max(salary)
from ( select salary from employee group by salary )
where rownum < N+1;

The dangerous thing about this statement is the missuse of the "group by" for ordering purposes. So, be carefull ...

Report message to a moderator

Re: Finding the Nth highest Salary value of given Employee Table [message #372604 is a reply to message #372600]

Tue, 27 February 2001 07:44

Joachim Lindner
Messages: 30
Registered: February 2001

Member

Report message to a moderator

Re: Finding the Nth highest Salary value of given Employee Table [message #372612 is a reply to message #372600]

Tue, 27 February 2001 12:49

Bala
Messages: 205
Registered: November 1999

Senior Member

Hi,

SQL> select empid, emp_name, salary
from emp a
where n > (select count(*)
from emp b
where b.salary > a.salary);

Bala.

Report message to a moderator

Re: Finding the Nth highest Salary value of given Employee Table [message #372614 is a reply to message #372600]

Wed, 28 February 2001 04:43

Joachim Lindner
Messages: 30
Registered: February 2001

Member

Perfect solution Bala !!! There is just a simple modification necessary to only get the data for the employee with the Nth highest salary ...

I know that you know but maybe he don't :-)

SQL> select empid, emp_name, salary
from emp a
where n-1 = (select count(*)
from emp b
where b.salary > a.salary);

Report message to a moderator

Re: Finding the Nth highest Salary value of given Employee Table [message #372676 is a reply to message #372600]

Fri, 02 March 2001 15:14

kayode Ogunmoroti
Messages: 2
Registered: March 2001

Junior Member

select salary
from emp
where rownum = nth
order 1 desc

The query will first sort and order the salary in decending order on the server before returning data to you, it's then able to Nth record you require

Report message to a moderator

Re: Finding the Nth highest Salary value of given Employee Table [message #373698 is a reply to message #372600]

Wed, 02 May 2001 06:01

Muralidhar Adapala
Messages: 1
Registered: May 2001

Junior Member

Please check your previous responses. There will be some problem in the output. If you try writing in the below manner you can get an exact output. But please remember, if you are looking for any performance related problems, then better go for PL/SQL Procedure only.

Solution:
SQL> select empno, ename, sal from emp a
where &n > (select count(distinct(sal))
from emp b
where b.sal > a.sal)
/

Report message to a moderator

Re: Finding the Nth highest Salary value of given Employee Table [message #374018 is a reply to message #373698]

Mon, 21 May 2001 00:19

yram
Messages: 75
Registered: February 2001

Member

select empno, ename, sal from emp a
where &n > (select count(distinct(sal))
from emp b
where b.sal > a.sal)
can u help me in explaing how does it works.

thanks
yram

Report message to a moderator

re: Finding the Nth highest Salary value of given Employee Table [message #374248 is a reply to message #373698]

Fri, 01 June 2001 19:54

Uday
Messages: 21
Registered: December 2000

Junior Member

Hi,
This is how it works..
All above answers seems to b not working...

select min(empno), min(ename), min(sal) from emp a
where 3 > (select count(distinct(sal))
from emp b
where b.sal > a.sal)

Report message to a moderator

Previous Topic:	Can we avoid using the (+) keyword in a outer join
Next Topic:	Optimized Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 12:53:08 CST 2024