OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Sql Query

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

Sql Query [message #6826]

Wed, 07 May 2003 10:49

sridhar
Messages: 119
Registered: December 2001

Senior Member

How i can get the max(sal) and second Max(sal) from emp table in a single query?

Report message to a moderator

Re: Sql Query [message #6828 is a reply to message #6826]

Wed, 07 May 2003 11:37

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

_{quote from sridhar:}
----------------------------------------------------------------------

How i can get the max(sal) and second Max(sal) from emp table in a single query?

----------------------------------------------------------------------
If you are on at least version 8.1.6, you can solve this problem using analytic functions:

SQL> SELECT   ename
  2  ,        sal
  3  ,        sal_rank
  4  FROM    (SELECT e.ename
  5           ,      e.sal
  6           ,      DENSE_RANK() OVER (ORDER BY sal DESC) sal_rank
  7           FROM   emp   e)
  8  WHERE    sal_rank IN (1,2)
  9  ORDER BY sal DESC
 10  ,        ename
 11  /
  
ENAME             SAL   SAL_RANK
---------- ---------- ----------
KING             5000          1
FORD             3000          2
SCOTT            3000          2
  
SQL>

If this result is not the one you had in mind, Sridhar, then can you provide us with the result set you are expecting, and we can probably reverse-engineer a query from that.

HTH,

A

Report message to a moderator

Re: Sql Query [message #6836 is a reply to message #6826]

Wed, 07 May 2003 14:18

Yuri de Abreu Seki
Messages: 2
Registered: May 2003

Junior Member

Hi,

Try to that:

SELECT sal
FROM emp
WHERE rownum <= 2
ORDER BY sal DESC

Abra�os,

Yuri Seki

Report message to a moderator

Re: Sql Query [message #6838 is a reply to message #6826]

Wed, 07 May 2003 14:34

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

_{quote from Yuri Seki:}
----------------------------------------------------------------------

Hi,

Try to that:

[i]SELECT sal[/i]
[i]FROM emp[/i]
[i]WHERE rownum <= 2[/i]
[i]ORDER BY sal DESC[/i]

Abra�os,

Yuri Seki

----------------------------------------------------------------------
This approach does not work. Viz:

SQL> SELECT * FROM emp ORDER BY sal DESC;
  
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
  
14 rows selected.
  
SQL> SELECT   *
  2  FROM     emp
  3  WHERE    ROWNUM <= 2
  4  ORDER BY sal DESC
  5  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
  
SQL>

As you can see, Allen and Smith do not earn the two maximum salaries.

You might be able to get by on the following:

SQL> SELECT *
  2  FROM  (SELECT   e.empno
  3         ,        e.ename
  4         ,        e.job
  5         ,        e.mgr
  6         ,        e.hiredate
  7         ,        e.sal
  8         ,        e.comm
  9         ,        e.deptno
 10         FROM     emp   e
 11         ORDER BY e.sal DESC)
 12  WHERE ROWNUM <= 2  
 13  /
  
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
  
SQL>

but the salary of $3000 is earned by two employees--Ford and Scott--and only Scott is shown. In cases like this where multiple employees earn identical, ranked salaries, this ROWNUM trick can give arbitrary results, so beware.

Be sure to get exact business requirements so you anticipate what the user community wants to see when multiple rows have the "second max sal", for instance.

Good luck,

A

Report message to a moderator

Re: Sql Query [message #6855 is a reply to message #6826]

Thu, 08 May 2003 13:14

Ronald Beck
Messages: 121
Registered: February 2003

Senior Member

Try this and see if it will do what you want...

select * from
(select sal from emp order by sal desc)
where rownum <=2;

HTH,
Ron

Report message to a moderator

Previous Topic:	Updating Constraint names
Next Topic:	date mistery

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 18:42:10 CDT 2024