Home » SQL & PL/SQL » SQL & PL/SQL » Calculate second highest salary
Calculate second highest salary Thu, 17 May 2007 06:13
 deep_gh1 Messages: 13Registered: 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
 caliguardo Messages: 107Registered: 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
 saibal Messages: 111Registered: 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
 JRowbottom Messages: 5933Registered: 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
 saibal Messages: 111Registered: 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
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
 deep_gh1 Messages: 13Registered: 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
 caliguardo Messages: 107Registered: 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
 saibal Messages: 111Registered: 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
 saibal Messages: 111Registered: 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
 caliguardo Messages: 107Registered: 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
 caliguardo Messages: 107Registered: 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
 JRowbottom Messages: 5933Registered: June 2006 Location: Sunny North Yorkshire, ho... Senior Member
Saibal:

```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.

`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
 JRowbottom Messages: 5933Registered: 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
 saibal Messages: 111Registered: 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
 caliguardo Messages: 107Registered: 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
 JRowbottom Messages: 5933Registered: 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
 JRowbottom Messages: 5933Registered: 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
 caliguardo Messages: 107Registered: February 2007 Location: Chennai Senior Member

"
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
 JRowbottom Messages: 5933Registered: 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
 saibal Messages: 111Registered: 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

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
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
 JRowbottom Messages: 5933Registered: 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.

```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 Jul 21 20:12:38 CDT 2017

Total time taken to generate the page: 0.13048 seconds