How to get the latest entry in each group
I am trying to get data from my table using a group. using the group works correctly, but I only need to grab the last inserted element of each group, but it doesn't work. my query always returns the first element of each group.
my request
SELECT id,type,userId,performDate,eventId FROM
`user_marker` where `eventId`='842' and DATE_FORMAT(from_unixtime(performDate),'%Y%c%d')
=DATE_FORMAT(now(),'%Y%c%d')
and `visibility`='1'GROUP BY type ORDER BY id DESC
+3
Portle74
source
to share
2 answers
Try
SELECT a.* FROM ( SELECT id,type,userId,performDate,eventId FROM
`user_marker` where `eventId`='842' and DATE_FORMAT(from_unixtime(performDate),'%Y%c%d')
=DATE_FORMAT(now(),'%Y%c%d') and `visibility`='1' ORDER BY id DESC ) as a GROUP BY a.type
0
Suman singh
source
to share
You can try as shown below -
SELECT b.id,b.type,b.userId,b.performDate,b.eventId
FROM user_marker b
JOIN (SELECT `type`,MAX(performDate)
FROM user_marker
WHERE `eventId`='842' AND DATE(FROM_UNIXTIME(performDate))=CURDATE() AND `visibility`='1'
GROUP BY `type`) a ON a.type=b.type AND a.performDate=b.performDate
ORDER BY b.`type`;
0
Zafar malik
source
to share