如何从此表中的每个不同用户中选择最高分数？ [英] How do I select the max score from each distinct user in this table?

问题描述

我有下表（得分）：

id  user  date         score  
---|-----|------------|--------
1  | 10  | 11/01/2016 | 400   
2  | 10  | 11/03/2016 | 450 
5  | 17  | 10/03/2016 | 305  
3  | 13  | 09/03/2016 | 120   
4  | 17  | 11/03/2016 | 300 
6  | 13  | 08/03/2016 | 120  
7  | 13  | 11/12/2016 | 120  
8  | 13  | 09/01/2016 | 110

我要选择 max（score）对于每个不同的用户，使用 date 作为平局（如果出现平局，应返回最新记录），结果如下所示（每个用户的最高得分，按得分降序排列）：

I want to select max(score) for each distinct user, using date as a tie-breaker (in the event of a tie, the most recent record should be returned) such that the results look like the following (top score for each user, sorted by score in descending order):

id  user  date         score  
---|-----|------------|--------
2  | 10  | 11/03/2016 | 450   
5  | 17  | 10/03/2016 | 305  
7  | 13  | 11/12/2016 | 120 

我正在使用Postgres，无论如何我都不是SQL专家。我尝试了类似于以下内容的操作，但由于我在 group by中没有包含 id 列，因此无法正常工作：

I'm using Postgres and I am not a SQL expert by any means. I've tried something similar to the following, which doesn't work because I don't have the id column included in the group by:

select scores.user, max(scores.score) as score, scores.id
from scores
group by scores.user
order by score desc

I有一种感觉，我需要进行子选择，但是我无法使联接正常工作。我发现如何我选择了具有MAX（Column value）的行，通过SQL中的另一列进行DISTINCT吗？但我似乎无法使任何解决方案对我有用，因为我需要返回该行的 id ，我可能会在 date 列上打平。

I have a feeling I need to do a sub-select, but I can't get the join to work correctly. I found How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL? but I can't seem to make any of the solutions work for me because I need to return the row's id and I have the possibility of a tie on the date column.

推荐答案

在Postgres中，通常最快的方法是在（）

In Postgres typically the fastest method is to use distinct on ()

select distinct on (user_id) *
from the_table
order by user_id, score desc;

这肯定比使用子查询的任何解决方案都要快很多使用 max（），通常仍然比使用窗口函数的等效解决方案要快一些（例如 row_number（））

That is definitely a lot faster then any solution using a sub-query with max() and usually still a bit faster then an equivalent solution using a window function (e.g. row_number())

^{我使用 user_id 作为列名，因为 user 是保留字，我强烈建议不要使用它。}

^{I used user_id for the column name because user is a reserved word and I strongly recommend to not use that.}

这篇关于如何从此表中的每个不同用户中选择最高分数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！