Home > Blogroll, sql > secondmax

secondmax

July 10th, 2008 Leave a comment Go to comments

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;

Tags:
  1. radino
    July 11th, 2008 at 07:11 | #1
    Reply | Quote

    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. AketiJyuuzou
    July 16th, 2008 at 12:56 | #2
    Reply | Quote

    My funny solution 8-)

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

  3. AketiJyuuzou
    July 16th, 2008 at 12:58 | #3
    Reply | Quote

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

    My funny solution 8-)

    [code]
    select RTrim(Ltrim(RegExp_Substr(XMLAgg(XMLElement("dummy",SAL) order by SAL desc),'>[^'),'

  4. AketiJyuuzou
    July 16th, 2008 at 13:02 | #4
    Reply | Quote

    Oh, code-tag of my post was disable.

    But I think that Laurent Schneider can understand my solution.

    I got hint from this article which used group_concat of MySQL.
    http://www.onlamp.com/pub/a/mysql/2007/03/29/emulating-analytic-aka-ranking-functions-with-mysql.html?page=4

  5. Laurent Schneider
    July 16th, 2008 at 13:34 | #5
    Reply | Quote

    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;

  6. Laurent Schneider
    July 16th, 2008 at 13:51 | #6
    Reply | Quote

    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

  7. Laurent Schneider
    July 16th, 2008 at 13:53 | #7
    Reply | Quote

    One more try to show what I learnt from Marco :mrgreen:

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

    SAL
    —-
    3000

  8. AketiJyuuzou
    July 16th, 2008 at 14:48 | #8
    Reply | Quote

    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

  9. Laurent Schneider
    July 16th, 2008 at 15:39 | #9
    Reply | Quote

    me too, but XML is huge!!! good luck

  10. Aketi Jyuuzou
    September 1st, 2009 at 22:48 | #10
    Reply | Quote

    Since 11gR2,
    We can use nth_Value 8-)

    http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions112.htm

    select distinct
    NTH_VALUE(sal,2)
    over(order by sal desc Rows between Unbounded Preceding
    and Unbounded Following)
    from EMP;

  11. Laurent Schneider
    September 2nd, 2009 at 08:24 | #11
    Reply | Quote

    sure, I thought about this too :)

    I regret that nth value has no aggregate form. Distinct+analytic is a bit ugly…

  1. January 6th, 2009 at 03:45 | #1
    Bookmarks about Example
CAPTCHA Image
*