Peter1987 Peter1987 - 1 year ago 54
SQL Question

Mysql group by day newest record

I wanna sort my list of prices by days to generate a chart. One day can have multiple prices, so I have to get the last entered on this day.
My current sql statement looks like this, but it takes the first entry of a day.

SELECT newprice, time FROM prices WHERE id = ? AND GROUP BY DAY(time) ORDER BY time DESC

Example of data to sort

How to fix it?

Answer Source

MySQL doesn't have a "last" function. You can approach this a different way:

SELECT p.newprice, p.time
FROM prices p
      p.time = (SELECT MAX(p2.time)
                FROM prices p2
                WHERE = AND
                      DATE(p2.time) = DATE(p.time)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download