0
429
ViewsSQL: 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.
·
Santiago Trujillo
3 answers
Answer question0
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;
·
Santiago Trujillo
Report
0
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
·
Santiago Trujillo
Report
0
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)
);
·
Santiago Trujillo
Report
Answer question