SQL select value by Maximum time per day

Question

DBMS : MS SQL 2005

Consider the following table as an example

[CurrencyID] ---- [Rate] ---- [ExchangeDate]

USD --------------- 1 ------ 08/27/2012 11:52 AM

USD -------------- 1.1 ----- 08/27/2012 11:58 AM

USD -------------- 1.2 ----- 08/28/2012 01:30 PM

USD --------------- 1 ------ 08/28/2012 01:35 PM

How can i get the rate of the latest [ExchangeDate] Per Day for each currency ?

The output would be :

 [CurrencyID] ---- [Rate] ---- [ExchangeDate]

    USD ----------- 1.1 ------- 08/27/2012

    USD ------------ 1 -------- 08/28/2012

What RDBMS are you using? In what way is this problem related to .NET at all? — Tim Schmelter, Aug 27 '13 at 09:02
possible duplicate of [How can I truncate a datetime in SQL Server?](http://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server) — Steven, Aug 27 '13 at 09:04

score 3 · Answer 1 · answered Aug 27 '13 at 09:05

3

You didn't specify which DBMS, following is Standard SQL:

select CurrencyID, Rate, ExchangeDate
from
  (
    select CurrencyID, Rate, ExchangeDate,
       row_number() 
       over (partition by CurrencyID, cast(ExchangeDate as date)
             order by ExchangeDate desc) as rn
    from tab
  ) as dt
 where rn = 1;

answered Aug 27 '13 at 09:05

dnoeth

57,618
3
33
50

Could you please Write this in MS SQL syntax ? – Siraj Mansour Aug 27 '13 at 10:05
The only part which is not SQL Server 2005 syntax is the CAST(ExchangeDate AS DATE). Steven posted a link on how to do this before SS2008: http://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server – dnoeth Aug 27 '13 at 10:15

score 3 · Accepted Answer · edited May 23 '17 at 12:16

3

For SQL 2008, the following does the trick:

SELECT  CurrencyID, cast(ExchangeDate As Date) as ExchangeDate , (
          SELECT   TOP 1 Rate
          FROM     Table T2
          WHERE    cast(T2.ExchangeDate  As Date) = cast(T1.ExchangeDate  As Date)
          AND      T2.CurrencyID = T1.CurrencyID
          ORDER BY ExchangeDate DESC) As LatestRate
FROM    Table T1
GROUP BY CurrencyID, cast(T1.ExchangeDate  As Date)

For anything below 2008, take a look here.

edited May 23 '17 at 12:16

Community

1
1

answered Aug 27 '13 at 09:11

Steven

159,023
23
313
420

1

In the sub query, you still need add the condition (T2.CurrencyID = T1.CurrencyID) right ? – Siraj Mansour Aug 27 '13 at 09:16
@SirajMansour: Correct. Updated my answer. – Steven Aug 27 '13 at 09:43
1

You Added T2.Rate = T1.Rate, the condition is (T2.CurrencyID = T1.CurrencyID) – Siraj Mansour Aug 27 '13 at 10:06
@SirajMansour: Right again. I wasn't really paying attention. – Steven Aug 27 '13 at 10:07
Awesome .. Thanks for the help – Siraj Mansour Aug 27 '13 at 10:08

Ehsan · Answer 3 · 2013-08-27T09:56:00.823

0

you can do this, read the format here

select * from exchangetable order by convert(datetime, ExchangeDate, 101) ASC desc


//101 = mm/dd/yyyy - 10/02/2008

edited Aug 27 '13 at 09:56

answered Aug 27 '13 at 09:03

Ehsan

30,264
6
53
63

Bart Friederichs · Answer 4 · 2013-08-27T10:11:06.290

0

For MySQL:

SELECT Rate, MAX(ExchangeDate) FROM table GROUP BY DATE(ExchangeDate)

Check out more information on aggregate functions.

Other RDBMS's might not support this (I know that PostgreSQL doesn’t).

edited Aug 27 '13 at 10:11

answered Aug 27 '13 at 09:04

Bart Friederichs

32,037
14
96
185

SQL select value by Maximum time per day

4 Answers4