CODEWITHSUNDEEP
sqlgroup-bysql-order-byoracle-apexgreatest-n-per-group
Paul
Paul

Reputation: 966

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 ?

Upvotes: 0

Views: 684

Answers (2)

Paul
Paul

Reputation: 966

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

Upvotes: 1

GMB
GMB

Reputation: 222482

Starting Oracle 12c, one option uses window functions in the order by clause and a fetch clause:

select mgr, ename, sal
from emp
where mgr is not null
order by rank() over(partition by mgr order by sal desc)
fetch first row with ties

Upvotes: 1

Related Questions