RE: Need help with SQL group by

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Do you care what value of plbbg1 is selected for the duplicate employee
record?
If not, then use any of the aggregate functions to 'pick' one, i.e.:

SELECT eeun, eedpt, plbemp, eenam, max(plbbg1) as plbbg1, plbhrs , plbptp
 FROM PRLBH, EEMP WHERE PLBEMP =  EEENO and 
PLBPTP in('CO','OH', '6D','7D','OG') and
 EETDT = 0 and   EEDPT = '081' and PLBWDT = 20090115 
GROUP BY PLBEMP,EENAM,PLBBG1, PLBHRS  , PLBPTP,EEUN, EEDPT  
 Order by PLBEMP            

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Need help with SQL group by

I have a sql statement that I cannot get to work the way I want.  
The statement as it currently exists is. 
 SELECT eeun, eedpt, plbemp, eenam, plbbg1, plbhrs , plbptp
 FROM PRLBH, EEMP WHERE PLBEMP =  EEENO and 
PLBPTP in('CO','OH', '6D','7D','OG') and
 EETDT = 0 and   EEDPT = '081' and PLBWDT = 20090115 
GROUP BY PLBEMP,EENAM,PLBBG1, PLBHRS  , PLBPTP,EEUN, EEDPT  
 Order by PLBEMP            

My results look like this 
	
Eeun	eedpt	plbemp  eenam		plbbg1   plbhrs   plbptp
Y	081	890	Smith, Mike	081 	4.00	OH
Y	081	890	Smith, Mike	081	1.00	OH
Y	081	1160	Mead,Kurt	081	3.50	OH
Y	081	1160	Mead,Kurt	081	1.50	OH

I am trying to get 1 record per employee, but I cannot find the correct
solution.  
What am I missing here?

Mike