返回在一个列上不同但在另一列上排序的记录 [英] Return records distinct on one column but order by another column

问题描述

我正在使用标准消息模型构建Rails 3应用程序。我想为每个唯一的session_id返回最近创建的消息记录。这似乎是一个相当简单的任务，但我无法编写代码或找到可行的解决方案。

不可否认，我也不是超级SQL精通者（因为我主要是通过Active Record查询获得的，远）。这是我要完成的工作。

I am building a Rails 3 app with a pretty standard message model. I would like to return the most recently created message records for each unique conversation_id. It seems like a fairly simple task, but I have not been able to code or find a working solution.
Admittedly, I am not super SQL savvy either (as I have gotten by with mainly Active Record queries thus far). Here is what I'm trying to accomplish.

示例消息表：

| id | sender_id | receiver_id | conversation_id | subject | body | created_at |
| 1  |     *     |      *      |        1        |    *    |   *  |    16:01   |
| 2  |     *     |      *      |        2        |    *    |   *  |    17:03   |
| 3  |     *     |      *      |        1        |    *    |   *  |    18:04   |
| 4  |     *     |      *      |        3        |    *    |   *  |    19:06   |
| 5  |     *     |      *      |        2        |    *    |   *  |    20:07   |
| 6  |     *     |      *      |        1        |    *    |   *  |    21:08   |
| 7  |     *     |      *      |        4        |    *    |   *  |    22:09   |

返回值我想只为每个<$ c $获得最最近的消息记录c> conversation_id 并由 created_at DESC 排序：

The return I would like to get only the most "recent" message record for each conversation_id and ordered by created_at DESC:

| id | sender_id | receiver_id | conversation_id | subject | body | created_at |
| 7  |     *     |      *      |        4        |    *    |   *  |    22:09   |
| 6  |     *     |      *      |        1        |    *    |   *  |    21:08   |
| 5  |     *     |      *      |        2        |    *    |   *  |    20:07   |
| 4  |     *     |      *      |        3        |    *    |   *  |    19:06   |

我在SQLite中的原始解决方案工作得很好： GROUP BY（conversation_id）。但是，显然该解决方案是SQLite独有的，不适用于Postgres。

My original solution in SQLite worked just fine: GROUP BY (conversation_id). However, apparently that solution is unique to SQLite and does not work with Postgres.

接下来，我尝试了： SELECT DISTINCT ON（conversation_id）* 。但是，这还需要 ORDER BY（conversation_id），这是我所不希望的。我想按 created_at 排序。

Next, I tried: SELECT DISTINCT ON (conversation_id) *. However, this also requires ORDER BY (conversation_id) which I do not want. I want to order by created_at.

推荐答案

直接打开

如果您使用 DISTINCT ON ，您需要一个子查询：

DISTINCT ON

If you use DISTINCT ON, you need a subquery for that:

SELECT *
FROM  (
   SELECT DISTINCT ON (conversation_id) *
   FROM   message t
   ORDER  BY conversation_id, created_at DESC
   ) sub
ORDER BY created_at DESC;

子查询中的顺序必须与 DISTINCT ON 子句，因此必须将其包装在外部查询中才能达到所需的排序顺序。

The order in the subquery must agree with the columns in the DISTINCT ON clause, so you must wrap it in an outer query to arrive at your desired sort order.

类似的故事，您还需要一个子查询：

Similar story, you need a subquery as well:

SELECT id, sender_id, receiver_id, conversation_id, subject, body, created_at
FROM  (
   SELECT *, row_number() OVER (PARTITION BY conversation_id
                                ORDER BY created_at DESC) AS rn
   FROM   message t
   ) sub
WHERE  rn = 1
ORDER  BY created_at DESC;

也可能较慢。

这篇关于返回在一个列上不同但在另一列上排序的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！