which is not right. Please help me with this. Thanks in advance
10-04-2000, 01:19 PM
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);
10-04-2000, 08:00 PM
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.
10-05-2000, 10:05 AM
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.
