CODEWITHSUNDEEP
sql
sm_
sm_

Reputation: 2602

SQL select value by Maximum time per day

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

Upvotes: 1

Views: 4140

Answers (5)

Artur Filip
Artur Filip

Reputation: 1

I know this is old but I thought it may be worth adding the QUALIFY clause into the mix due to how simple it is to read (without subqueries).

WITH example AS (
  SELECT 'USD' AS currency_id, 1 as rate, DATETIME(2012,08,27,11,52,00) as exchange_dateTime UNION ALL
  SELECT 'USD', 1.1, DATETIME(2012,08,27,11,58,00) UNION ALL
  SELECT 'USD', 1.2, DATETIME(2012,08,28,01,30,00) UNION ALL
  SELECT 'USD', 1, DATETIME(2012,08,28,01,35,00) 
)

SELECT *
FROM example
QUALIFY
  MAX(exchange_dateTime) OVER (PARTITION BY currency_id, datetime_trunc(exchange_dateTime,DAY)) = exchange_dateTime

Upvotes: 0

Bart Friederichs
Bart Friederichs

Reputation: 33573

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).

Upvotes: 0

Steven
Steven

Reputation: 172835

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.

Upvotes: 3

Ehsan
Ehsan

Reputation: 32719

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

Upvotes: 0

dnoeth
dnoeth

Reputation: 60502

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;

Upvotes: 3

Related Questions