How to group by rows with most recent order?

Question

Updated:

Here is the demo and current result based on M Khalid Junaid's answer. The query still doesn't output my expected result.

I have a very simple table and here is the values.

id      animal_id       latitude    longitude   created_at
--------------------------------------------------------------------------
119     75          1.356203    103.828140  2014-04-30 15:00:04
118     75          1.296613    103.857079  2014-04-30 14:58:58
117     75          1.296613    103.857079  2014-04-30 14:58:20
116     75          1.296613    103.857079  2014-04-30 14:53:17

Here is my query and I want to GROUP if latitude, longitude and user_id is same.

select p.id,p.animal_id,p.name,p.latitude,p.longitude,p.created_at from Photo p
        where 5 >= (select count(*)
                    from Photo p2
                    where p2.animal_id = p.animal_id and
                          p2.id <= p.id
                   )
        AND DATE(p.created_at) > DATE_SUB(NOW(),INTERVAL 13 DAY)
        AND ( p.latitude BETWEEN 0.908862 AND 1.717581 ) AND ( p.longitude BETWEEN 103.584595 AND 104.098206 )
        GROUP BY p.latitude,p.longitude,p.animal_id
        ORDER BY p.created_at DESC;

Current result id = 116

id      animal_id       latitude    longitude   created_at
--------------------------------------------------------------------------
119     75          1.356203    103.828140  2014-04-30 15:00:04
116     75          1.296613    103.857079  2014-04-30 14:53:17

Expected result id = 118

I want to get most recent result when I group latitude,longitude,uer_id

id      animal_id       latitude    longitude   created_at
--------------------------------------------------------------------------
119     75          1.356203    103.828140  2014-04-30 15:00:04
118     75          1.296613    103.857079  2014-04-30 14:58:58

I've tried several ways but couldn't get the desired result.

animal_id is together in the same table so no need to JOIN any outside table. — Devyn, May 11 '14 at 04:11
There is no animal_id in the data set. Either include it in the data set, or exclude it from the query — Strawberry, May 11 '14 at 09:23
Oops! my bad. I've updated the question. user_id is supposed to be animal_id. Please check here http://www.sqlfiddle.com/#!2/7bd16/1 — Devyn, May 11 '14 at 10:50
You want to group by "latitude, longitude and user_id[/uer_id]" (another column missing from the example above) - so why are you grouping by animal_id? — Strawberry, May 11 '14 at 11:10

score 1 · Answer 1 · answered May 10 '14 at 18:45

Use a self join,results from group by are indeterminate and can't guarantee you the latest row for the group

select p.id,p.animal_id,
p.latitude,p.longitude,p.created_at 
from Photo p
JOIN (SELECT MAX(id) id,latitude,longitude,animal_id
 FROM Photo 
GROUP BY latitude,longitude,animal_id
) p1 ON(p.id = p1.id)
where 5 >= (select count(*)
            from Photo p2
            where p2.animal_id = p.animal_id and
            p2.id <= p.id)
AND DATE(p.created_at) > DATE_SUB(NOW(),INTERVAL 13 DAY)       
AND ( p.latitude BETWEEN 0.908862 AND 1.717581 ) 
AND ( p.longitude BETWEEN 103.584595 AND 104.098206 )
GROUP BY p.latitude,p.longitude,p.animal_id
ORDER BY p.created_at DESC;

Demo

Thanks for your answer. I've updated the dataset based on my current DB. Please help me check again if possible. The dataset I shared here is the result of 1st execution so it's not correct at all. Sorry about that. — Devyn, May 11 '14 at 04:09

score 0 · Answer 2 · answered May 11 '14 at 11:48

Finally I've managed to find my own answer. Thanks everyone for sharing your time.

SELECT p.id,p.animal_id,p.latitude,p.longitude,p.created_at 

-- This will make sure that `GROUP BY` will pickup most recent result
FROM (SELECT id,animal_id,latitude,longitude,created_at from Photo ORDER BY id DESC) p

-- Pick 5 rows for each animal_id. Meaning, there can be multiple photo of animal but MAX is 5.
WHERE 5 >= (select count(*)
                    from (SELECT * from Photo p4 GROUP BY latitude,longitude,animal_id) p2
                    where p2.animal_id = p.animal_id and
                          p2.id >= p.id 
                   )

-- We want Photos uploaded 5 days ago
AND DATE(p.created_at) > DATE_SUB(NOW(),INTERVAL 15 DAY)

-- Plus, only photos within given Map Bound
AND ( p.latitude BETWEEN 0.908862 AND 1.717581 ) AND ( p.longitude BETWEEN 103.584595 AND 104.098206 )                   

-- This will help to remove duplicate images in same location of same animal. We will see only most recent photo in each exact lat,lng location
GROUP BY p.latitude,p.longitude,p.animal_id

ORDER BY id DESC;

score 0 · Answer 3 · answered May 11 '14 at 11:50

0

It's may be tricky solution. You can concatenate latitude,longitude,animal_id in the group statement like this :

group by concat(latitude, longitude, animal_id)

answered May 11 '14 at 11:50

dermawan

1
1

How to group by rows with most recent order?

3 Answers3

Demo