CODEWITHSUNDEEP
mysqlsql
AlvinH
AlvinH

Reputation: 1469

SQL: Select the last record for each day given datetime

I have a table of stock data (the db is MySQL):

trade_time          price  
2013-01-02 09:43:00 515.61
2013-01-03 09:39:00 525.90
2013-01-03 11:38:00 539.44
2013-01-03 13:22:00 509.16
2013-01-04 09:47:00 507.40
2013-01-04 14:33:00 517.45
2013-01-07 09:33:00 550.42
2013-01-07 13:13:00 524.85
2013-01-07 14:51:00 536.44

I would like to return the last traded price for each day

trade_date price  
2013-01-02 515.61
2013-01-03 509.16
2013-01-04 517.45
2013-01-07 536.44

What makes this question different from other "selecting the latest record based on datetime" questions on this site is that input is in datetime but output is in date. Let me know this question has already been answered.

Upvotes: 5

Views: 14312

Answers (5)

Shiva_Kishore
Shiva_Kishore

Reputation: 39

Try with DATEDIFF instead.

SELECT trade_time, price
FROM 
(Select DATEDIFF(DAY,LEAD(tradetime) OVER(ORDER BY tradetime desc),LAG(tradetime) OVER(ORDER BY tradetime desc) ) diff, price from yourTable )subquery
Where diff=1

Upvotes: 0

Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7571

I understand this is an implementation in MY-SQL, but I tried in SQL-Server just for fun and this query gives the requisite output:

SELECT CAST(SQ.TT AS DATE) AS TRADED_DATE, PRICE
FROM STOCK
INNER JOIN
(SELECT MAX(TRADE_TIME) AS TT
FROM STOCK
GROUP BY CAST(TRADE_TIME AS DATE)) SQ
ON SQ.TT = TRADE_TIME

Output as 

TRADED_DATE PRICE
----------- ---------------------------------------
2013-01-02  515.61
2013-01-03  509.16
2013-01-04  517.45
2013-01-07  536.44

(4 rows affected)

Upvotes: 1

Malta
Malta

Reputation: 544

Here is an efficient solution using window function ROW_NUMBER() over a type cast of the timestamp column to date:

select trade_date, price
from (
    select trade_date, price, row_number() over
        (partition by date(trade_date) 
         order by trade_date desc) as row_num 
    from stock) as subquery
where row_num = 1
order by trade_date

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

You may join to a subquery which finds the maximum datetime for each date.

SELECT t1.trade_time, t1.price
FROM yourTable t1
INNER JOIN
(
    SELECT DATE(trade_time) AS trade_date, MAX(trade_time) AS max_trade_time
    FROM yourTable
    GROUP BY DATE(trade_time)
) t2
    ON t2.trade_date = DATE(t1.trade_time) AND
       t2.max_trade_time = t1.trade_time
ORDER BY
    t1.trade_time;

screen capture of demo below

Demo

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use a correlated subquery:

select t.*
from t
where t.trade_date = (select max(t2.trade_date)
                      from t t2
                      where date(t2.trade_date) = date(t.trade_date)
                     );

Upvotes: 0

Related Questions