sql query on employ table

Question

My employees table has 3 columns:

emp_id,
emp_sal,
dept_id.

I need to find, for each department, the employee(s) with the greatest salary. I need to return emp_id, emp_sal and dept_id for those employees.

Thanks in advance

score 4 · Answer 1 · answered Mar 11 '13 at 02:18

ORACLE supports common table expression and windowing functions,

WITH employees_sal
AS
(
    SELECT  emp_id,
            emp_sal,
            dept_id,
            DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY emp_sal DESC) ranks
    FROM    employee
)
SELECT  emp_id, emp_sal, dept_id
FROM    employees_sal
WHERE   ranks = 1

SQLFiddle Demo

score 3 · Answer 2 · edited Mar 11 '13 at 20:14

3

SELECT EMP_ID,EMP_SAL,DEPT_ID 
FROM EMP
WHERE (DEPT_ID,EMP_SAL) IN (SELECT DEPT_ID,MAX(EMP_SAL) 
                           FROM EMP GROUP BY DEPT_ID)

edited Mar 11 '13 at 20:14

APC

144,005
19
170
281

answered Mar 11 '13 at 02:23

Aspirant

2,238
9
31
43

@Aspirant- Are you sure it's a valid *Oracle* syntax? – PM 77-1 Mar 11 '13 at 02:54

David R. · Answer 3 · 2013-03-11T21:11:24.703

2

There's no need to use CTE.

SELECT a.emp_id, a.emp_sal, a.dept_id
FROM employ a
WHERE a.emp_sal = 
    (SELECT MAX(b.emp_sal)
    FROM employ b
    WHERE b.dept_id = a.dept_id
    AND A.emp_id = B.emp_id)

edited Mar 11 '13 at 21:11

answered Mar 11 '13 at 02:20

David R.

281
2
14

Equally, there's no need to use a correlated subquery either. Both methods are fine. – David Aldridge Mar 11 '13 at 07:22
Judging by the reputation of the asker, I just wanted to use something they would likely be able to understand easier than learning CTE. This query is common practice in Oracle DB for PeopleSoft, so it's likely to be seen in other code you interact with. – David R. Mar 11 '13 at 12:31
This query returns the employees whose salary matches a maximum departmental salary. It does not return the employees who are the highest paid in each department. – APC Mar 11 '13 at 20:18
Good call, I left off a line. This is extremely similar to code I use every single day. – David R. Mar 11 '13 at 21:12

score 0 · Answer 4 · answered Mar 11 '13 at 18:46

SELECT * FROM 
( 
 SELECT deptno, ename, sal
     , MAX(sal) OVER (PARTITION BY deptno) dept_max_sal 
     , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal desc) rno
   FROM scott.emp 
 )
 WHERE sal = dept_max_sal
  -- AND rno = 1 -- Optional to eliminate same salary employee --
 ORDER BY deptno, sal DESC, ename
/

DEPTNO    ENAME    SAL    DEPT_MAX_SAL    RNO
-----------------------------------------------
10        KING     5000    5000           1
20        FORD     3000    3000           2    -- same sal --
20        SCOTT    3000    3000           1    -- same sal --
30        BLAKE    2850    2850           1

sql query on employ table

4 Answers4