Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
craigwg
Posting Yak Master
154 Posts |
 Posted - 2011-08-24 : 13:19:30
|
| I have a table of exchange rates that looks like this:3 columns:subsidiaryid, effectivedate, eurorateeach subsidiaryid has multiple entries, one for each month (effectivedate) to keep track of the applicable eurorate for that month.I am tasked with making a query that displays one row for each subsidiaryid, displaying the max(effective) date and its corresponding eurorate. I would normally do a select statement that groups by subsidiaryid and shows the max(effectivedate). But I just want the eurorate, not the sum, max, min or any other aggregate value.So how can I set this query up?Thanks for your help!Craig Greenwood |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-24 : 13:26:19
|
you would use it to join to main table like belowselect t.*from table tinner join (select subsidiaryid,max(effectivedate) as latest from table group by subsidiaryid)t1on t.subsidiaryid = t1.subsidiaryidand t.effectivedate = t1.latest ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2011-08-24 : 13:59:06
|
| PERFECT. I have such a hard time trying to determine when to use derived tables. Very helpful. Thank you so much! I owe you a coke!Craig Greenwood |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-25 : 01:33:53
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Group by problem