PostgreSQL 不允许我按顺序对列进行分组

Question

在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太短的答案都是正确的。我将继续在我的项目中同时使用答案和模式，随着时间的推移，我可以理解哪一个是最好的 - 我猜 - 。因此，选择其中之一并继续您的工作。你会没事的。

上次更新：有时，"不同打开"会从结果中排除某些 ID。假设我有一个 id 列，我有 6 行是相同的。因此，区别于将其与结果 BUT rank(( 排除在结果中。所以，使用rank((！

Answer 1

与MySQL不同，PostgreSQL不会显示未在聚合查询中聚合的列的随机数据。

解决方案在错误消息中

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

这意味着在选择此列时，您必须按"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

Answer 2

您希望使用 rank() 窗口函数对每个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

这将为您提供样本中带有 2、3 和 6 id行。您可能希望在over中添加辅助排序键，以便在同一日期每useridx多封邮件时始终做出选择。

你至少需要PostgreSQL 8.4(AFAIK(才能拥有窗口函数。

Answer 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;

在某些情况下，这可以比其他方法更好地扩展。

Answer 4

多年后，但你不能只在 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中对我有用

Answer 5

您正在聚合结果。

这意味着用户3将只有一行，而不是 2 行。但是，您也可以为聚合行选择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);