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

59,503
4
39
56

Could you please Write this in MS SQL syntax ? – sm_ 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

166,672
24
332
435

1

In the sub query, you still need add the condition (T2.CurrencyID = T1.CurrencyID) right ? – sm_ 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) – sm_ Aug 27 '13 at 10:06
@SirajMansour: Right again. I wasn't really paying attention. – Steven Aug 27 '13 at 10:07

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

31,833
6
56
65

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

33,050
15
95
195

SQL select value by Maximum time per day

4 Answers4