# secondmax

How do I retrieve the second highest salary of emp?

There is no right or wrong treatment of duplicates, there are only buggy specifications…

There are plenty of ways to do this.

An Oracle 7 approach would be

``````SELECT MAX(SAL)
FROM EMP, (
SELECT MAX(SAL) MAXSAL FROM EMP)
WHERE SAL<MAXSAL;
``````

Using TOP-n queries I could use

``````
SELECT SAL
FROM (
SELECT ROWNUM R,SAL FROM (
SELECT SAL FROM EMP ORDER BY SAL DESC))
WHERE R=2;
``````

Using ROW_NUMBER (or RANK or DENSE_RANK), I could use

``````
SELECT SAL
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) R,SAL FROM EMP)
WHERE R=2;
``````

Another approach would be to create a SecondMax aggregate function, as the following example from the doc
Example 11-12 How to Create and Use a User-Defined Aggregate Function

``````SELECT SecondMax(sal)
FROM emp;``````

Ok, if I know I have 14 employees in EMP, retrieving the second highest is way easier ðŸ™‚

``````
SELECT PERCENTILE_DISC(2/14) WITHIN GROUP (ORDER BY SAL DESC)
FROM EMP;
``````

## 13 Replies to “secondmax”

1. I would not use RANK()..

``````
SQL> SELECT ENAME, SAL FROM (
2    SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC)
3  WHERE ROWNUM < 5;

ENAME            SAL
---------- ---------
KING         5000,00
SCOTT        3000,00
FORD         3000,00
JONES        2975,00

SQL>
SQL>
SQL> SELECT SAL
2  FROM (
3    SELECT RANK() OVER (ORDER BY SAL DESC) R,SAL FROM EMP)
4  WHERE R=2;

SAL
---------
3000,00
3000,00

SQL>
SQL>
SQL> UPDATE EMP
2  SET SAL=5000
3  WHERE ENAME='JONES';

1 row updated

SQL>
SQL>
SQL> SELECT SAL
2  FROM (
3    SELECT RANK() OVER (ORDER BY SAL DESC) R,SAL FROM EMP)
4  WHERE R=2;

SAL
---------

SQL>
SQL> ROLLBACK;

Rollback complete
``````

2. My funny solution ðŸ˜Ž

select RTrim(Ltrim(RegExp_Substr(XMLAgg(XMLElement(“dummy”,SAL) order by SAL desc),’>[^’),’

3. Oh I post again.
I have used code-tag.

My funny solution ðŸ˜Ž

[code]
select RTrim(Ltrim(RegExp_Substr(XMLAgg(XMLElement(“dummy”,SAL) order by SAL desc),’>[^’),’

4. no, I do not see your query but I could imagine something like

``````
select
RegExp_Substr(
XMLAgg(
XMLElement(SAL,SAL) order by SAL desc),
'<.*?>.*?</.*?>'
,1,2)
FROM EMP;
``````

5. Ok, let’s do it with XMLish functions ðŸ™‚

select extractvalue(XMLAgg(XMLElement(SAL,SAL) order by SAL desc),’/SAL[2]’) FROM EMP;

EXTRACTVALUE(XMLAGG(XMLELEMENT(SAL,SAL)ORDERBYSALDESC),’/SAL[2]’)
——————————————————————————–
3000

6. One more try to show what I learnt from Marco

select extractvalue(XMLAgg(XMLElement(SAL,SAL) order by SAL desc),’//SAL[2]/text()’) SAL FROM EMP

SAL
—-
3000

7. Wow!!

greate solution.
I did not realize to use extractValue.
I will study XML.

In my old posts, my usage of code-tag was wrong.
My solution was below.

``````
select RTrim(Ltrim(
RegExp_Substr(XMLAgg(XMLElement("dummy",SAL)
order by SAL desc),'>[^>]+<',1,2),'>'),'<') sal
from scott.emp;
``````

SAL
—-
3000