Peter1987 Peter1987 - 1 month ago 9
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
WHERE p.id = ? AND
      p.time = (SELECT MAX(p2.time)
                FROM prices p2
                WHERE p2.id = p.id AND
                      DATE(p2.time) = DATE(p.time)
               )
ORDER BY p.time DESC;