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

В качестве примера я хотел бы выбрать id с максимальной датой по категориям, результат: 7, 2, 6

id  category  date
1   a         2013-01-01
2   b         2013-01-03
3   c         2013-01-02
4   a         2013-01-02
5   b         2013-01-02
6   c         2013-01-03
7   a         2013-01-03
8   b         2013-01-01
9   c         2013-01-01

Могу ли я узнать, как это сделать в PostgreSQL?

Ответ 1

Это идеальный прецедент для DISTINCT ON (специальное расширение Postgres стандарта DISTINCT)

SELECT DISTINCT ON (category)
       id
FROM   tbl
ORDER  BY category, "date" DESC;

Осторожно в порядке убывания сортировки. Если столбец может быть NULL, вы можете добавить NULLS LAST:

Сортировка PostgreSQL по дате времени asc, null сначала?

DISTINCT ON является самым простым и быстрым. Подробное объяснение в этом связанном ответе:

Выберите первую строку в каждой группе GROUP BY?

Для больших таблиц рассмотрим этот альтернативный подход:

Оптимизировать групповой максимальный запрос

Оптимизация производительности для многих строк на category:

Оптимизировать запрос GROUP BY для получения последней записи на пользователя

Ответ 2

Попробуйте следующее:

SELECT * FROM Table1 t1
JOIN 
(
   SELECT category, MAX(date) AS MAXDATE
   FROM Table1
   GROUP BY category
) t2
ON T1.category = t2.category
AND t1.date = t2.MAXDATE

См. этот SQLFiddle

Ответ 3

Другой подход заключается в использовании функции окна first_value: http://sqlfiddle.com/#!12/7a145/14

SELECT DISTINCT
  first_value("id") OVER (PARTITION BY "category" ORDER BY "date" DESC) 
FROM Table1
ORDER BY 1;

... хотя я подозреваю, что предложение hims056 будет, как правило, лучше работать там, где есть соответствующие индексы.

Третье решение:

SELECT
  id
FROM (
  SELECT
    id,
    row_number() OVER (PARTITION BY "category" ORDER BY "date" DESC) AS rownum
  FROM Table1
) x
WHERE rownum = 1;

Ответ 4

SELECT id FROM tbl GROUP BY cat МАШИНА (дата)