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 |
|
qman
Constraint Violating Yak Guru
442 Posts |
 Posted - 2011-09-28 : 11:22:59
|
| I have a currency rate table consisting of the following structure.CREATE TABLE Currency_Rate]( [CFrom] [char](4) NULL, [CTo] [char](4) NULL, [CRate] [float] NULL, [CDate] [smalldatetime] NULL)It currently contains the following test data:CFROM CTO CRATE CDATEUSD CAN 0.111 2011-09-20 00:00:00USD CAN 0.111 2011-09-20 00:00:00USD CAN 0.222 2011-09-20 00:00:00CAN USD 0.001 2011-09-15 00:00:00CAN USD 0.111 2011-09-20 00:00:00I am trying to do the following:Retreive all rows with a unique currency rate combination (CFROM AND CTO), based on the most recent Date.I have this so far:select distinct CFrom, CTo from Currency_Ratewhere CDate = (select max(CDate) from Currency_Rate)How do I include the remaining columns (CRate, CDate) in my result set....?Thanks in advance! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-28 : 11:31:29
|
| [code]SELECT CFROM,CTO,CRATE,CDATEFROM(SELECT CFROM,CTO,CRATE,CDATE,ROW_NUMBER() OVER (PARTITION BY CFROM,CTO ORDER BY CDATE DESC) AS rnFROM Currency_Rate)tWHERE rn=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2011-09-28 : 11:35:25
|
| Visa, you rule...!That worked perfect, I need to read up on how "OVER" works....Thanks bud! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-28 : 11:37:50
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Query Assistance