geeks geeks - 6 months ago 13
SQL Question

How to get last record of each day in mysql?

I want to get last record of each day in mysql.

Location<id, date, place_id>
table has multiple entries on each day. This Location table has place_id and time at which place_id is inserted.

Also taking consider if place_id is not present then return second last record which has place_id. In following table for
NULL, '2016-04-06 18:52:06'
record we are returning
'13664', '2016-04-06 12:57:30'
, which is second last record on '2016-04-06' (6th March) and has place_id.

One more thing, on single day, there would be more place_id, see the following table..

id || place_id || date
'1', '47', '2016-04-05 18:09:37'
'2', '48', '2016-04-05 12:09:37'
'3', '13664', '2016-04-06 12:57:30'
'4', '9553', '2016-04-08 10:09:37'
'5', NULL, '2016-04-06 18:52:06'
'6', '9537', '2016-04-07 03:34:24'
'7', '9537', '2016-04-07 03:34:24'
'8', '656', '2016-04-07 05:34:24'
'9', '7', '2016-04-07 05:34:57'


When I run following query it returns following result

Query I run the following query but it is giving me wrong result

`Location<id, place_id, date>`

select L1.place_id, L1.date from
Location1 L1
Left join
Location1 L2
on
Date(L1.date) = Date(L2.date)
And
L1.date < L2.date
where
L2.date is null

group by L1.date;


Result I want:

id....place_id ........date
'1', '47', '2016-04-05 18:09:37'
'3', '13664', '2016-04-06 12:57:30'
'4', '9553', '2016-04-08 10:09:37'
'9', '7', '2016-04-07 05:34:57'

Answer

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]