-
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
-
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);
-
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.
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|