Как группировать по строкам с самым последним порядком?

Question

Обновлено:

Вот демо и текущий результат, основанный на ответе М. Халида Джунаида. Запрос все еще не выводит мой ожидаемый результат.

У меня есть очень простая таблица и вот значения.

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

Вот мой запрос, и я хочу, чтобы GROUP, если широта, долгота и user_id совпадают.

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;

Текущий результат 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

Ожидаемый результат id = 118

Я хочу получить самый последний результат, когда я группирую широту, долготу, 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

Я пробовал несколько способов, но не смог получить желаемый результат.

Answer 1

Используйте самостоятельное объединение, результаты от группы не определены и не могут гарантировать вам последнюю строку для группы

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;

демонстрация

Answer 2

Это может быть сложным решением. Вы можете объединить широту, долготу,animal_id в выражении группы следующим образом:

group by concat(latitude, longitude, animal_id)

Answer 3

Наконец мне удалось найти свой ответ. Спасибо всем за ваше время.

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;