Group by question

Database
Enthusiast

Group by question


Hi,

I need to do something like this in one query. Is there any function/way to do this ? I want to select the record  (name,empno,medstat,edlev) having max salary, but just grouping on medstat,edlev. I know ideally we should group on 1,2,3,4, but this is my requirement.

sel  name,empno,medstat,edlev,max(salary) from retail.employee

group by 3,4

The alternate way is to do like this. But my agreegation is actually very large ~200million records, so i want to do it in one step as part of query tuning


sel  name,empno,medstat,edlev,salary from retail.employee

where (medstat,edlev,salary) in

(sel medstat,edlev,max(salary)

from retail.employee

group by 1,2)

4 REPLIES
Enthusiast

Re: Group by question

Try following:

sel  name,empno,medstat,edlev,max(salary) over ( partition by medstat,edlev )from retail.employee

Enthusiast

Re: Group by question

Hi Chanchal,

This is not giving the desired o/p. If finds the max(salary) for  medstat,edlev. But it appends this will all the occurances of name,empno. But i want only that employee that has this max salary.





Name EmpNo MedStat EdLev Group Max(Salary)
Jennifer Pat Clerk#000000622 N 0 1,04,913.36
Seth Vernon Clerk#000000490 N 0 1,04,913.36
Mary O'Rourk Clerk#000000774 N 0 1,04,913.36
Sarah Jordon Clerk#000000671 N 0 1,04,913.36
Jason Gross Clerk#000000338 N 0 1,04,913.36

Where as it should only give 

Name EmpNo MedStat EdLev Group Max(Salary)
Seth Vernon Clerk#000000490 N 0 1,04,913.36
         

The value 104913.36 is correct though.

Enthusiast

Re: Group by question

Why don't you create separate temp table and then join to achieve desired result.

Junior Contributor

Re: Group by question

sel * from retail.employee
qualify rank() over (partition by medstat,edlev order by salary desc) = 1;

The Group By columns are moved into Partition By and MIN/MAX is Order By Asc/Desc.

Dieter