选择最近的分组依据(或其他解决方案...) [英] Selecting most recent as part of group by (or other solution ...)

问题描述

我有一个表，其中重要的列如下所示:

I've got a table where the columns that matter look like this:

username
source
description

我的目标是获取10条最新记录，其中用户/源组合是唯一的.根据以下数据:

My goal is to get the 10 most recent records where a user/source combination is unique. From the following data:

1 katie    facebook    loved it!
2 katie    facebook    it could have been better.
3 tom      twitter     less then 140
4 katie    twitter     Wowzers!

查询应返回记录2、3和4(假设ID值更高，则更新-实际表使用timestamp列).

The query should return records 2,3 and 4 (assume higher IDs are more recent - the actual table uses a timestamp column).

我当前的解决方案有效"，但需要1个选择才能生成10条记录，然后需要1个选择才能获得每行的正确描述(因此11个选择可以生成10条记录)...我不得不想像有一种更好的方法可以去.该解决方案是:

My current solution 'works' but requires 1 select to generate the 10 records, then 1 select to get the proper description per row (so 11 selects to generate 10 records) ... I have to imagine there's a better way to go. That solution is:

SELECT max(id) as MAX_ID, username, source, topic
FROM   events
GROUP BY source, username
ORDER BY MAX_ID desc;

它返回正确的ID，但是描述错误，因此我可以通过记录ID选择正确的描述.

It returns the proper ids, but the wrong descriptions so I can then select the proper descriptions by the record ID.

推荐答案

未经测试，但是您应该可以使用join处理此问题:

Untested, but you should be able to handle this with a join:

SELECT
    fullEvent.id,
    fullEvent.username,
    fullEvent.source,
    fullEvent.topic
FROM
    events fullEvent JOIN
    (
        SELECT max(id) as MAX_ID, username, source
        FROM   events
        GROUP BY source, username
    ) maxEvent ON maxEvent.MAX_ID = fullEvent.id
ORDER BY fullEvent.id desc;

这篇关于选择最近的分组依据(或其他解决方案...)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！