Query

[some]

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

[tamilselvan]

select a.empl_uno,
a.rate,
a.eff_date,
a.last_modify
from rate a
where a.eff_date = ( select max(b.eff_date)
from rate b
where a.empl_uno = b.empl_uno);

[carp]

I believe the following will work if you are using Oracle:

select a.empl_uno,
a.rate,
a.eff_date,
a.last_modify
from rate a, (SELECT empl_uno, max(eff_date) max_date
FROM rate
GROUP BY empl_uno) b
where a.eff_date = b.max_date;

This should be more efficient since it eliminates the correlated subquery.

[tamilselvan]

The cost will be higher if we use Group By where as the correlated query will give less cost.

I have analyzed both my query and Carp's query. I found that my query uses Sort Aggregate and Carp's query uses Sort Group by.

I feel SORT AGGREGATE will be faster than SORT GROUP BY.

Carp, do you have experience on this?