How to retrieve the last record in each group

Question

I am trying to get data from my table using group by . using group by works correctly but i need to take only last inserted item of every group but its not work. my query always return first item of each group.

my query

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

possible duplicate of [Groupwise maximum](http://stackoverflow.com/questions/15211479/groupwise-maximum) — CBroe, Jun 29 '15 at 06:41

score 0 · Answer 1 · answered Jun 29 '15 at 06:41

Please 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

Zafar Malik · Answer 2 · 2015-06-29T07:32:23.373

0

You can try as per 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`;

edited Jun 29 '15 at 07:32

answered Jun 29 '15 at 06:48

Zafar Malik

6,734
2
19
30

thanks @Strawberry to draw my attention on it, actually I was checking unixtime format, if it convert in same mysql format, so that we can avoid one extra date_format functino. – Zafar Malik Jun 29 '15 at 07:13
Still missing that alias – Strawberry Jun 29 '15 at 07:16

How to retrieve the last record in each group

2 Answers2