0

So I have the following table:

CREATE TABLE Trades(
    TradeTime DATETIME,
    TradePrice DECIMAL(10,2)
);

INSERT INTO Trades VALUES("2013-01-02 09:43:00", 515.61);
INSERT INTO Trades VALUES("2013-01-03 09:39:00", 525.90);
INSERT INTO Trades VALUES("2013-01-03 11:38:00", 539.44);
INSERT INTO Trades VALUES("2013-01-03 13:22:00", 509.16);
INSERT INTO Trades VALUES("2013-01-04 09:47:00", 507.40);
INSERT INTO Trades VALUES("2013-01-04 14:33:00", 517.45);
INSERT INTO Trades VALUES("2013-01-07 09:33:00", 550.42);
INSERT INTO Trades VALUES("2013-01-07 13:13:00", 524.85);
INSERT INTO Trades VALUES("2013-01-07 14:51:00", 536.44);

I want to output a 2 column table, showing the final trade price per day, as well as the given date (not including time) for that price.

My attempted code (below) outputs each given day, however instead of the final trade price, it outputs the first price that was inserted into the table for each given day. How do I return the final price instead of the first one?

SELECT MAX(DATE(TradeTime)) AS Date, TradePrice
FROM Trades
GROUP BY DATE(TradeTime);
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Which MySQL version are you using? – Giorgos Betsos Jan 09 '21 at 16:58
  • select tmp.trade_date,t.TradeTime,t.TradePrice from Trades t join (SELECT date(tradetime) trade_date, Max(tradetime) tradetime FROM Trades group by date(tradetime) ) as tmp on tmp.tradetime=t.tradetime order by tmp.trade_date – Mohammed Jubayer Jan 09 '21 at 17:23

0 Answers0