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

Tags:

Mysql