Grouping records by using aggregate function Oracle

Question

I have a Table in Oracle as below

   CREATE TABLE Employees(EmpId INT, 
                          EmpName VARCHAR2(30), 
                          Designation VARCHAR2(30), 
                          Salary INT);

I have inserted rows in table as below

INSERT ALL 
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(101, 'Scott', 'Clerk', 2500)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(102, 'Mac', 'Manager', 5000)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(103, 'Steave', 'Clerk', 1500)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(104, 'John', 'Clerk', 1500)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(105, 'Jack', 'Analyst', 2500)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(106, 'Paul', 'Manager', 4500)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(107, 'Ryan', 'Clerk', 1250)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(108, 'Phillipe', 'Analyst', 3150)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(109, 'Clark', 'Clerk', 1200)
   INTO Employees(EmpId, EmpName, Designation, Salary)VALUES(110, 'Arnold', 'Clerk', 1100)
   SELECT * FROM dual;

Below is the Short overview of Table

enter image description here

Now I want to get the Top paid people grouped by designation as Below

EmpName   Designation  Salary
Phillipe  Analyst      3150 
Scott     Clerk        2500
Mac       Manager      5000

I want empName, Designation and max Salary by designation.

I Tried the below query but it is bringing all records

SELECT EmpName, Designation, max(Salary) AS msal
  FROM Employees
 GROUP BY Designation, EmpName 
 ORDER BY Designation, msal DESC

Thanks for Reply

Your EMPLOYEES table doesn't have a Department. So do you mean empName, Designation and max Salary by designation ? Either way, please correct your question. — APC
– APC, Commented Jul 4, 2013 at 7:10

peterm · Accepted Answer · 2013-07-04 07:20:08Z

3

A version with a correlated subquery

SELECT EmpName, Designation, Salary
  FROM Employees e
 WHERE Salary = (SELECT MAX(Salary) 
                   FROM Employees 
                  WHERE Designation = e.Designation)

or with a JOIN

SELECT e.EmpName, e.Designation, e.Salary
  FROM Employees e JOIN 
(
  SELECT Designation, MAX(Salary) Salary
    FROM Employees 
   GROUP BY Designation
) q ON e.Designation = q.Designation
   AND e.Salary = q.Salary

Here is SQLFiddle demo for both queries

edited Jul 4, 2013 at 7:20

answered Jul 4, 2013 at 7:14

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2013-07-04 07:19:20Z

Assuming you mean 'designation' rather than 'department', you can do this with an analytic query:

SELECT EmpName, Designation, Salary
FROM (
    SELECT EmpName, Designation, Salary,
        RANK() OVER (PARTITION BY Designation ORDER BY Salary DESC) AS rn
      FROM Employees
)
WHERE rn = 1
ORDER BY Designation;

EMPNAME                        DESIGNATION                        SALARY
------------------------------ ------------------------------ ----------
Phillipe                       Analyst                              3150 
Scott                          Clerk                                2500 
Mac                            Manager                              5000

The inner query applies a ranking to each row, with 1 representing the highest salary in each designation (and allows ties). The outer query then restircts the resulsts to only show the highest-ranking row(s) for each designtation.

In your original verion you're group by isn't really doing anything because it includes effectivley all columns - except empid, but that doesn't make a difference unless you have two people with the same name. You'd need to find the max for the designation, and then find the records which matched that max value, which would mean looking at the same table; for example:

SELECT EmpName, Designation, Salary
  FROM Employees
 WHERE (Designation, Salary) IN (
     SELECT Designation, MAX(Salary)
       FROM Employees
      GROUP BY Designation
  )
ORDER BY Designation;

... or peterm's correlated subquery; but I prefer the analytic route, particularly for larger tables, because you only hit the table once. And I find it clearer, personally.

schurik · Accepted Answer · 2013-07-04 07:12:51Z

2

SELECT 
  max(EmpName) keep (dense_rank last order by salary) AS EmpName
, Designation
, max(Salary) keep (dense_rank last order by salary)  AS Salary
FROM Employees
GROUP BY Designation
;

answered Jul 4, 2013 at 7:12

schurik

7,9582 gold badges26 silver badges30 bronze badges

Collectives™ on Stack Overflow

Grouping records by using aggregate function Oracle

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related