Какую функцию агрегации использовать в запросе с GROUP BY?

Question

[Everything_is_not_so_bad]

Есть выборка данных, которую я получил по SQL запросу

SELECT ref_id, id, created_at FROM "images"
GROUP BY ref_id, id
ORDER BY created_at desc
LIMIT 20

id	ref_id	created_at
25553	1	2014-06-07 13:38:46
25408	1	2014-06-07 13:36:42
25105	1	2014-06-07 13:32:43
25068	1	2014-06-07 13:32:16
24986	670	2014-06-07 01:53:49
24978	670	2014-06-07 01:53:44
24977	670	2014-06-07 01:53:43
24862	666	2014-06-07 01:52:15
24050	602	2014-06-07 01:33:43
24051	602	2014-06-07 01:33:43
20963	1818	2014-06-06 03:16:09
19681	1723	2013-06-07 01:37:50
19680	1723	2013-06-07 01:37:49
18452	236	2013-06-07 01:14:36
16913	517	2012-10-05 01:37:09
16750	658	2012-10-05 01:36:34
15229	696	2012-10-05 01:18:52
14416	1192	2012-10-04 21:41:37
14417	1192	2012-10-04 21:41:37
13107	749	2012-03-24 20:08:59

Ожидал получить в выборке не более одной строки с каждым ref_id, выбрав из images с данным ref_id только с самой поздней датой.
Типа такого:

id	ref_id	created_at
25553	1	2014-06-07 13:38:46
24986	670	2014-06-07 01:53:49
24862	666	2014-06-07 01:52:15
24050	602	2014-06-07 01:33:43
20963	1818	2014-06-06 03:16:09
19681	1723	2013-06-07 01:37:50
18452	236	2013-06-07 01:14:36
16913	517	2012-10-05 01:37:09
16750	658	2012-10-05 01:36:34
15229	696	2012-10-05 01:18:52
14416	1192	2012-10-04 21:41:37
13107	749	2012-03-24 20:08:59

Какую функцию агрегации использовать в запросе с GROUP BY, чтобы достичь нужного? Помогите составить запрос.

Answer 1

[Melkij]

Ожидал получить в выборке не более одной строки с каждым ref_id

GROUP BY ref_id, id

С чего бы это вы ожидали увидеть только один ref_id, если явно сказали затем группировать по id?
С учётом названия и того, что postgresql в принципе согласился запрос выполнить - id является первичным ключом. А группировка по первичному ключу ожидаемо результат не изменит.

select  distinct on (ref_id)  ref_id,  id,  created_at from tablename  order by ref_id, created_at desc;

Comments

[Everything_is_not_so_bad]

Правильно было написать "Ожидаю", а не "Ожидал". Изначальный запрос работал в SQLite, но Postgres жаловался на ошибку, а я пытался понять как правильно и так и не получилось.

[Melkij]

Не думаю, что работал в sqlite. Исполнялся и возвращал какой-то случайный результат - да. Может даже иногда похожий на правду. Осмысленный результат - нет. Например, любую дату, вместо последней. Иначе бы postgresql со своим нативным запретом на доступ к неаггрегированным данным при наличии группировки согласился бы с вашим запросом.

[Everything_is_not_so_bad]

Melkij: Точно работал с GROUP BY ref_id, но, скорее всего, возвращал случайную строку.

Answer 2

[Александр Аксентьев]

не знаю что на счет group by
Но обычно это делается joinом в саму себя и выбором нужной(максимальной) даты.
Либо через max() пробовать вытащить нужную дату.
Либо select * from (подзапрос где группировка и сортировка по макс дате)
Либо просто отсортировать по дате тот запрос который есть.

В общем куча вариантов, какой именно выбирайте сами.