Search code examples
sqlgroup-bysql-order-byoracle-apexgreatest-n-per-group

SQL Group by, but allow duplicate if value is the same

I'm trying to group some max values by an ID (until here i got it figured out), and I also need to select the names from the persons with the max values, so my grouping should allow duplicates if two persons with that grouped ID have the same value (max).

Here's what I've got so far.

SELECT MGR,MAX(SAL) AS MaxSal
FROM EMP
WHERE MGR IS NOT NULL
GROUP BY MGR

Now I also need to extract the field ENAME, how could I do this, while grouping and also allowing duplicate groups when necessary ?

Solution

  • The solution is analytic functions. Here's how I achieved my desired result.

    SELECT MGR,ENAME,SAL 
FROM
(
   SELECT MGR,ENAME,SAL,
   MAX(SAL) OVER (PARTITION BY MGR) AS MaxSal
   FROM EMP
)
WHERE SAL=MaxSal