PostgreSQL не позволяет мне группировать столбец с порядком

В PostgreSQL я хочу получать сразу всех пользователей и заказывать их по дате.

Это мой запрос:

SELECT id, useridx, isread, message, date
  FROM messages
 WHERE isread = 1
 GROUP BY useridx
 ORDER BY date DESC

Это пример данных:

------------------------------------------------------
+  id  |  useridx |  isread  |  messsage |  date     +
------------------------------------------------------
   1   |  1       |  0        | Hello    |  2012-01-01    
   2   |  2       |  1        | Hi       |  2012-01-02    
   3   |  3       |  1        | Test     |  2012-01-03    
   4   |  3       |  0        | My Msg   |  2012-01-04    
   5   |  4       |  1        | sadasd   |  2012-01-05    
   6   |  4       |  1        | sdfsdfd  |  2012-01-06    
   7   |  4       |  0        | sdfsdfsd |  2012-01-07    
   8   |  5       |  0        | 5345634  |  2012-01-08
   9   |  6       |  0        | sdfdfsd  |  2012-01-09
   10  |  7       |  0        | sdfsdfsf |  2012-01-10
------------------------------------------------------

Теперь, что я хочу сделать, это получить эту таблицу, группируя их через useridx и упорядочивая по дате.

Ожидаемый результат:

------------------------------------------------------
+  id  |  useridx |  isread  |  messsage |  date     +
------------------------------------------------------  
   6   |  4       |  1        | sdfsdfd  |  2012-01-06 
   3   |  3       |  1        | Test     |  2012-01-03  
   2   |  2       |  1        | Hi       |  2012-01-02    
------------------------------------------------------

Фактический результат

ERROR:  column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function

Я тоже не хочу группировать дату. Я просто хочу сгруппировать с useridx и отсортировать их по дате DESC.

Любая помощь/идея оценена!

~~Примечание. Я также попробовал Distinct.~~ ~~Не соответствует моим потребностям, или я сделал это неправильно.~~

Я очень смущен и застрял между методами DISTINCT ON и rank().

Вывод: для тех, кто получает ту же проблему, здесь можно прочитать это как ответ. Оба @kgrittn и @mu слишком короткие ответы правильные. Я продолжу использовать оба ответа и схемы в своем проекте, и со временем я смогу понять, какой из них является лучшим -i guess-. Итак, выберите один из них и продолжайте свою работу. Вы будете в порядке.

Последнее обновление: иногда, Distinct On исключает некоторые идентификаторы из результата. Допустим, у меня есть столбец id, и у меня есть 6 строк, которые одинаковы. Итак, отличная от exlude это от результата BUT rank() просто результат. Итак, используйте rank()!

Ответ 1

Вы хотите использовать функцию окна rank() для упорядочивания результатов в каждой группе useridx а затем useridx первый из них, обернув ранжированные результаты в производную таблицу:

select id, useridx, isread, message, date
from (
    select id, useridx, isread, message, date,
           rank() over (partition by useridx order by date desc) as r
    from messages
    where isread = 1
) as dt
where r = 1

Это даст ваши строки с id 2, 3 и 6 из вашего образца. Возможно, вы захотите добавить дополнительный ключ сортировки в over чтобы последовательно сделать выбор, когда у вас есть несколько сообщений на useridx в одну дату.

Вам понадобится хотя бы PostgreSQL 8.4 (AFAIK), чтобы иметь функции окна.

Ответ 2

PostgreSQL, в отличие от MySQL, не показывает случайные данные для столбцов, которые не агрегированы в агрегированном запросе.

Решение в сообщении об ошибке

ERROR:  column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function

Это означает, что вы должны GROUP BY столбец "messages.date" или использовать агрегатную функцию, такую как MIN() или MAX() при выборе этого столбца

Пример:

SELECT MIN(id), useridx, isread, message, MAX(date)
FROM messages WHERE isread = 1 
GROUP BY useridx, isread, message
ORDER BY MAX(date) DESC

Ответ 3

Другой вариант - использовать SELECT DISTINCT ON (который сильно отличается от простого SELECT DISTINCT):

SELECT *
  FROM (SELECT DISTINCT ON (useridx)
            id, useridx, isread, message, date
          FROM messages
          WHERE isread = 1
          ORDER BY useridx, date DESC) x
  ORDER BY date DESC;

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

Ответ 4

Вы агрегируете результаты.

Это означает, что вместо 2 строк для пользователя 3 вас будет только одна строка. Но вы также выбираете столбцы id, message, isread для агрегированной строки. Как PostgreSQL должен предоставлять эти данные? Должно ли оно быть max() возможных значений? Может быть, min()?

Я предполагаю, что вы хотели бы иметь данные о новейших сообщениях. Попробуйте этот запрос:

SELECT id, useridx, isread, message, date FROM messages
 WHERE isread = 1 AND (useridx, date) IN
  (SELECT useridx, max(date) FROM messages WHERE isread = 1 GROUP BY useridx);

Ответ 5

Годы спустя, но вы не можете просто заказать в подзапросе FROM:

SELECT m.id, m.useridx, m.isread, m.message, m.date
FROM (
   SELECT m2.id, m2.useridx, m2.isread, m2.message, m2.date 
   FROM message m2 
   ORDER BY m2.id ASC, m2.date DESC
) m
WHERE isread = 1
GROUP BY useridx

Это работает для меня в PostgreSQL 9.2