How to get last record of each day in mysql?
You may give it a try:
SELECT
L.id,
L.place_id,
L.date
FROM Location L
INNER JOIN
(
SELECT
MAX(date) max_time
FROM Location
GROUP BY Date(`date`)
) AS t
ON L.date = t.max_time
SQL FIDDLE DEMO
SQL FIDDLE DEMO2
[Based on your expected output]
Can you try with the following query:
SELECT * FROM `Location` GROUP BY DATE(`date`) ORDER BY `date` DESC
What this query does is group the rows by descending date and show a row for each date.
Get the last record:
SELECT * FROM `Location` ORDER BY `date` LIMIT 1;
Get the last record that doesn't have a null as a value:
SELECT * FROM `Location` WHERE place_id IS NOT NULL ORDER BY `date` LIMIT 1;
Get records for all the places which are not null:
SELECT * FROM `Location` WHERE place_id IS NOT NULL GROUP BY `place_id` ORDER BY `date` DESC