Home » SQL & PL/SQL » SQL & PL/SQL » Calculate second highest salary
Calculate second highest salary [message #238290] Thu, 17 May 2007 06:13 Go to next message
deep_gh1
Messages: 13
Registered: October 2006
Location: India
Junior Member

Can anybody here helping me to find out how to calculate the second highest salary of an employee table???...

Any kind contribution will be highly appreciated....
Thanks,
deep
Re: Calculate second highest salary [message #238296 is a reply to message #238290] Thu, 17 May 2007 06:20 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Can u try this?

"select sal ,s from
(select a.*,rownum s from (select * from emp order by sal desc) a) where s = 2"

[Updated on: Thu, 17 May 2007 06:38]

Report message to a moderator

Re: Calculate second highest salary [message #238297 is a reply to message #238290] Thu, 17 May 2007 06:20 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
scott@DBASE-SQL>>select ename, sal from emp e
  2  where 1=(select count(*) from emp where e.sal<sal);

ENAME             SAL
---------- ----------
SCOTT            3000
FORD             3000

scott@DBASE-SQL>>
Re: Calculate second highest salary [message #238301 is a reply to message #238296] Thu, 17 May 2007 06:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Can u try this?

"select sal from (select a.*,rownum s from emp a) where s = 2"
Well, the o/p could try it, but seeing as it will give the wrong answer, why should they bother.
Proof of wrongness:
drop table emp;

create table emp (ename  varchar2(30), empno  number, sal  number);

insert into emp values ('Henry',1,1000);
insert into emp values ('Thomas',3,500);
insert into emp values ('James',4,750);
insert into emp values ('Gordon',2,1000);
insert into emp values ('Edward',5,300);

SQL> select sal from (select a.*,rownum s from emp a) where s = 2;

       SAL
----------
       500

Using the same data:
  1  select ename, sal from emp e
  2* where 1=(select count(*) from emp where e.sal<sal)
SQL> /

no rows selected


The OP needs to define what they want to happen in the cases where two people have the same highest salary, or the same second highest salary, or both before this question is answerable.
Re: Calculate second highest salary [message #238303 is a reply to message #238296] Thu, 17 May 2007 06:33 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
This query won't give the correct answer:


scott@DBASE-SQL>>select sal from (select a.*,rownum s from emp a) where s = 2;

       SAL
----------
      1600
scott@DBASE-SQL>>select ename, sal from (select * from emp order by sal desc);

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300
WARD             1250
MARTIN           1250
ADAMS            1100
JAMES             950
SMITH             800

14 rows selected.
Re: Calculate second highest salary [message #238304 is a reply to message #238303] Thu, 17 May 2007 06:39 Go to previous messageGo to next message
deep_gh1
Messages: 13
Registered: October 2006
Location: India
Junior Member

But the above doesn't work properly....it gives wrong answare...
500 is not the second highest salary....the o/p should be 750...
Re: Calculate second highest salary [message #238307 is a reply to message #238290] Thu, 17 May 2007 06:42 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

"scott@DBASE-SQL>>select ename, sal from emp e
2 where 1=(select count(*) from emp where e.sal<sal);"

This will not work always. If he wants the third max sal, then have to go for something different.
Re: Calculate second highest salary [message #238309 is a reply to message #238301] Thu, 17 May 2007 06:50 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Two people earning the same salary which is the highest in the group, as is the case in the table created by you returns no rows, bec. my query interprets that as 'there being no person earning the second highest salary. I can define at least three very different and reasonable interpretations of the OP's request,each producing an entirely different result set--so your assertion that you are demonstrating a proof of wrongness is in my opinion not entirely fair, to say the least.
Regards
Re: Calculate second highest salary [message #238311 is a reply to message #238307] Thu, 17 May 2007 06:54 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Can you tell me why this will not work?
The third highest sal:
s
cott@DBASE-SQL>>select ename, sal from emp e
  2  where 2=(select count(distinct sal) from emp where e.sal<sal);

ENAME             SAL
---------- ----------
JONES            2975

scott@DBASE-SQL>>
Re: Calculate second highest salary [message #238312 is a reply to message #238309] Thu, 17 May 2007 06:56 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Also u can try this


"
select sal,s from (select sal,row_number() over( order by sal desc) s from emp ) where s = 2

"


U can very well try using rownum.If that doesn't work Analytical queries are more efficient

[Updated on: Thu, 17 May 2007 06:57]

Report message to a moderator

Re: Calculate second highest salary [message #238317 is a reply to message #238311] Thu, 17 May 2007 07:06 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

yup, I agree. This is how the query should look like.


"
@DBASE-SQL>>select ename, sal from emp e
2 where &n=(select count(distinct sal) from emp where e.sal<sal);
"

for (n+1)th max salary.

[Updated on: Thu, 17 May 2007 07:13]

Report message to a moderator

Re: Calculate second highest salary [message #238318 is a reply to message #238307] Thu, 17 May 2007 07:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Saibal:

Your solution:
cott@DBASE-SQL>>select ename, sal from emp e
  2  where 2=(select count(distinct sal) from emp where e.sal<sal);
will not work (as I demonstrated above) if there are three or more records in EMP sharing the highest salary.

Caliguardo:
It's not that analytics are more efficient - they aren't.
What they do is force you to include an ORDER BY clause that guarantees that the order of the results.

If you run your query:
select sal,s from (select sal,row_number() over( order by sal desc) s from emp ) where s = 2 ;
aginst the test data I posted above, you'll see that it is still wrong - it returns a value of 1000, which is not the second highest salary by any calculation.

[Updated on: Thu, 17 May 2007 07:11]

Report message to a moderator

Re: Calculate second highest salary [message #238319 is a reply to message #238318] Thu, 17 May 2007 07:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If I were the OP, I would look at the RANK or DENSE_RANK analytics as my best solution. You have to decide what you are doing in the event of duplicate salary records, but this should give you enough to work from:
  1  select ename
  2        ,sal
  3        ,rank() over (order by sal desc)       rank
  4        ,dense_rank() over (order by sal desc) dense_rank
  5* from   emp
SQL> /

ENAME                                 SAL       RANK DENSE_RANK
------------------------------ ---------- ---------- ----------
Henry                                1000          1          1
Gordon                               1000          1          1
James                                 750          3          2
Thomas                                500          4          3
Edward                                300          5          4
Re: Calculate second highest salary [message #238320 is a reply to message #238318] Thu, 17 May 2007 07:16 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
While I agree with you that the OP should give us what exactly he means by second highest salary,
the fact that you are somewhat presumptuously stating that you have a workaround to offer, lets have it. I am very interested in seeing how you interpret 'second highest salary'
Re: Calculate second highest salary [message #238324 is a reply to message #238318] Thu, 17 May 2007 07:19 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Jrowbottom:

What's ur word on this?


"select sal ,s from
(select a.*,rownum s from (select * from emp order by sal desc) a) where s = 2"

This should work ,I guess.

Analytical queries simplify our process and i have read in an article that they are more efficient in performance than other ones.
Re: Calculate second highest salary [message #238327 is a reply to message #238320] Thu, 17 May 2007 07:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't interpret 'second highest salary' in any fixed way. The interpretation of it depends entirely on the situation.

The solution I posted above has advantages over both the other solutions.

Unlike
select sal,s from (select sal,row_number() over( order by sal desc) s from emp ) where s = 2 ;
it will never return the highest salary and tell you that it is the second highest, and unlike:
select ename, sal from emp e
where 2=(select count(distinct sal) from emp where e.sal<sal);
if you use DENSE_RANK, it will always return you some data. In fact, looking at it, the above query is just an inefficient way of getting the dense_rank for a single value.
Re: Calculate second highest salary [message #238328 is a reply to message #238324] Thu, 17 May 2007 07:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Calliguardo - do you actually read more than the first line of my posts.
Here, in greater detail, is a big flaw in your solution. Cut and paste this into SQL*Plus and you will be able to run it yourself:
drop table emp;

create table emp (ename  varchar2(30), empno  number, sal  number);

insert into emp values ('Henry',1,1000);
insert into emp values ('Thomas',3,500);
insert into emp values ('James',4,750);
insert into emp values ('Gordon',2,1000);
insert into emp values ('Edward',5,300);

SQL> select sal,s from (select sal,row_number() over( order by sal desc) s from emp ) where s = 2 ;

       SAL          S
---------- ----------
      1000          2
Your query is saying, in this example, that the second highest salary is 1000, which is patently untrue.
Re: Calculate second highest salary [message #238331 is a reply to message #238328] Thu, 17 May 2007 07:35 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

How about this?

"
select sal ,s from
(select a.*,rownum s from (select distinct(a.sal) from emp a order by sal desc) a) where s = 2

"

If even the above query doesn't work? Is there a solution without using analytical queries ?

[Updated on: Thu, 17 May 2007 07:37]

Report message to a moderator

Re: Calculate second highest salary [message #238333 is a reply to message #238331] Thu, 17 May 2007 07:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That is basically calculating the Dense_Rank, which will do fine (once you change that 4 to a 2) if that's what is needed.

What I am saying (and that no one seems interested in) is that only the OP knows the answer to this question, as only the OP knows what they need to do in the case of duplicate salarys.
Re: Calculate second highest salary [message #238414 is a reply to message #238318] Thu, 17 May 2007 22:02 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
@J. Rowbottom

Quote:
Saibal:

Your solution:

cott@DBASE-SQL>>select ename, sal from emp e
2 where 2=(select count(distinct sal) from emp where e.sal<sal);

will not work (as I demonstrated above) if there are three or more records in EMP sharing the highest salary.


Let's take a table where the highest salary is shared by three people

scott@DBASE-SQL>>create table employee as select * from emp;

Table created.



  1  insert into employee(empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2 values(7840, 'HOGGARD', 'CEO', NULL, '17-MAY-07', 5000, NULL, 20)
scott@DBASE-SQL>>/

1 row created.

scott@DBASE-SQL>>insert into employee(empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values(7841, 'PLUNKETT','CFO', NULL, '17-MAY-07', 5000, NULL, 30);

1 row created.

scott@DBASE-SQL>>commit;

Commit complete.

scott@DBASE-SQL>>select empno, ename, sal from employee order by sal desc;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7839 KING             5000
      7840 HOGGARD          5000
      7841 PLUNKETT         5000
      7788 SCOTT            3000
      7902 FORD             3000
      7566 JONES            2975
      7698 BLAKE            2850
      7782 CLARK            2450
      7499 ALLEN            1600
      7844 TURNER           1500
      7934 MILLER           1300
      7521 WARD             1250
      7654 MARTIN           1250
      7876 ADAMS            1100
      7900 JAMES             950
      7369 SMITH             800

16 rows selected.

scott@DBASE-SQL>>


So, we have a table employee that has three people earning the exact same salary. Let's see if I can get the second highest salary using the query that I had proffered.

scott@DBASE-SQL>>select empno, ename, sal from employee e
  2  where 1=
    (select count(distinct sal) from employee where  e.sal<sal; 

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000
      7902 FORD             3000

scott@DBASE-SQL>>
Let's try the third highest salary:
scott@DBASE-SQL>>select empno, ename, sal from employee e
  2  where 2=(select count(distinct sal) from employee where e.sal<sal);

     EMPNO ENAME             SAL
---------- ---------- ----------
      7566 JONES            2975

Now, the fourth highest salary:
scott@DBASE-SQL>>select empno, ename, sal from employee e
  2  where 3=(select count(distinct sal) from employee where e.sal<sal);

     EMPNO ENAME             SAL
---------- ---------- ----------
      7698 BLAKE            2850

scott@DBASE-SQL>>

Ok, they all seem to work.
If you read my posts, sequentially, you will see that all along I agreed with you that the term 'second highest salary' is open to various interpretations, and the OP is the best person to tell us exactly what he requires. But, I differ with you on your view that the queries that I have given produce a wrong answer--they don't, at least I don't see them!
Having said that, I think analytics is the best way to solve top n or related queries, and if you are suggesting that, then I guess, there is nothing to disagree there!
Re: Calculate second highest salary [message #238457 is a reply to message #238414] Fri, 18 May 2007 02:53 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Saibal:

I would appear to have posted in haste, and not read exactly what you were saying, I'm afraid, and I apologise for that.

Your original query:
select ename, sal from emp e
where 1=(select count(*) from emp where e.sal<sal);
had a problem which meant it would sometimes return no rows if there were duplicate salaries.
I'm afraid I didn't immediately spot that you'd changed the query, and that your new query actually worked fine, despite my claims to the contrary.

I just ran your final version and the analytic version through a timing harness, and the analytic version seems to run in about 60% of the time, so yes - analytics look like the way to go for this problem.
Previous Topic: LOOP INSERT INTO PROCEDURE
Next Topic: DML stmt inside Functions
Goto Forum:
  


Current Time: Fri Dec 09 06:17:07 CST 2016

Total time taken to generate the page: 0.07291 seconds