Hi All,

I want to write a query for Rate table. It has four columns empl_uno,rate,eff_date,last_modify. It has multiple rates for single employee.

emlp_uno rate eff_date last_modify
----------------------------------------------------------------
207 375.00 1/1/1990 11/23/1999 4:10:47 PM
207 395.00 4/22/2000 5/18/2000 3:13:01 PM
347 235.00 1/1/1990 11/8/1999 8:20:46 PM
347 235.00 4/22/2000 5/18/2000 3:27:21 PM
347 250.00 8/1/2000 9/25/2000 1:12:44 PM
375 130.00 1/1/1999 9/25/2000 1:24:44 PM
375 150.00 8/1/2000 9/25/2000 1:29:44 PM

I want to return latest rates for employee for example

emlp_uno rate eff_date last_modify
----------------------------------------------------------
207 395.00 4/22/2000 5/18/2000 3:13:01 PM
347 250.00 8/1/2000 9/25/2000 1:12:44 PM
375 150.00 8/1/2000 9/25/2000 1:29:44 PM

I wrote query like
select empl_uno,rate,max(eff_date),last_modify from Rate
group by empl_uno,rate,eff_date,last_modify

and I get result as

emlp_uno rate eff_date last_modify
----------------------------------------------------------------
207 375.00 1/1/1990 11/23/1999 4:10:47 PM
207 395.00 4/22/2000 5/18/2000 3:13:01 PM
347 235.00 1/1/1990 11/8/1999 8:20:46 PM
347 235.00 4/22/2000 5/18/2000 3:27:21 PM
347 250.00 8/1/2000 9/25/2000 1:12:44 PM
375 130.00 1/1/1999 9/25/2000 1:24:44 PM
375 150.00 8/1/2000 9/25/2000 1:29:44 PM

which is not right. Please help me with this. Thanks in advance