postgresql - 根据两个字段过滤行，其中一个字段包含选择条件

Question

给出下表

group | weight | category_id | category_name_plus
    1       10           100   Ab
    1       20           101   Bcd
    1       30           100   Efghij
    2       10           101   Bcd
    2       20           101   Cdef
    2       30           100   Defgh
    2       40           100   Ab
    3       10           102   Fghijkl
    3       20           101   Ab

“权重”对于每个组都是唯一的，也是组内记录顺序的指标。

我想要的是检索每个按 category_id 过滤的组的记录，但只有在其“组”中具有最高“权重”的记录。

按 category_id = 100 过滤的示例:

group | weight | category_id | category_name_plus
    1       30           100   Efghij
    2       40           100   Ab

按 category_id = 101 过滤的示例:

group | weight | category_id | category_name_plus
    1       20           101   Bcd
    2       20           101   Cdef
    3       20           101   Ab

我怎样才能只选择这些行？

我尝试摆弄 UNIQUE、MAX(category_id) 等，但我仍然无法获得正确的结果。我的主要问题是在此处获取 category_name_plus 值。

我正在使用 PostgreSQL 9.4(测试版 3)，因为我还需要各种其他细节，例如 "WITH ORDINALITY" 等。

Answer 1

排名 window function应该做的伎俩:

SELECT "group", weight, category_id, category_name_plus
FROM   (SELECT "group", weight, category_id, category_name_plus,
               RANK() OVER (PARTITION BY "group" 
                            ORDER BY weight DESC) AS rk
        FROM   my_table) t
WHERE  rk = 1 AND category_id = 101

注意:
“group”是 SQL 中的保留字，因此必须用引号括起来才能用作列名。不过，将其替换为非保留字(例如“group_id”)可能会更好。