SQL: Select the last record for each day given datetime

user_11612543 0
Viewed 5607

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.

mysql sql
4 Answers

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

user_18632290

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
user_10418120

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)
                     );
user_11440350

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)
user_6536220
Related