CODEWITHSUNDEEP
sqlsql-server
Carlos80
Carlos80

Reputation: 433

Select Values Based On Most Recent Date

I have a currency table which is updated every few months. I'm trying to pull out only the latest numbers based on the date. However, it doesn't seem to matter what filter or data type convertor I put on it. I can't pull out my unique list.

I'm not sure how to copy over the table format. There are only the following three columns: 

Date,
CCY,
Rate

The table is designed with this date format:

2014-01-06 12:07:38.000 GBP   1.65459525585175

Any help would be much appreciate.

Upvotes: 0

Views: 81

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5094

Having table like this:

CREATE TABLE [dbo].[ExRate](
    [Date] [datetime] NULL,
    [CCY] [char](3) NULL,
    [Rate] [decimal](12, 6) NULL
) ON [PRIMARY]

The correct code is:

with cte1 as
(
Select Date, CCY, Rate, ROW_NUMBER() over (partition by CCY order by Date desc) rn1
from ExRate
)
select * from cte1 where rn1=1

Upvotes: 2

Related Questions