All Forums Database
teradatauser2 236 posts Joined 04/12
05 Jun 2012
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)

 

 

05 Jun 2012

 

Try following:

 

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

 

Regards
Chanchal Preet Singh

teradatauser2 236 posts Joined 04/12
06 Jun 2012

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.

 

Jigar 70 posts Joined 09/11
06 Jun 2012

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

 

 

dnoeth 4628 posts Joined 11/04
09 Jun 2012
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

 

Dieter

You must sign in to leave a comment.