sql - Postgresql Get max value of each group with multiple columns

Question

I have the following table

CREATE table test
(
    random_number INT,
    band VARCHAR(255),
    member_name VARCHAR(255)
);

INSERT INTO test VALUES(2300,'Metallica', 'Kirk');
INSERT INTO test VALUES(2000,'Metallica', 'Lars');
INSERT INTO test VALUES(2500,'Metallica', 'James');
INSERT INTO test VALUES(2800,'Metallica', 'Roberto');
INSERT INTO test VALUES(100,'SkidRow', 'Sebastian');
INSERT INTO test VALUES(140,'SkidRow', 'Rachel');
INSERT INTO test VALUES(110,'SkidRow', 'Scott');
INSERT INTO test VALUES(150,'SkidRow', 'Dave');
INSERT INTO test VALUES(100,'SkidRow', 'Rob');
INSERT INTO test VALUES(500,'Motorhead', 'Lemmy');
INSERT INTO test VALUES(100,'Motorhead', 'Mikkey');
INSERT INTO test VALUES(200,'Motorhead', 'Phil');

How could I get the biggest random_number of each band and return something like this:

random_number |   band    | member_name
-----------------------------------------
     2800     | Metallica |  Roberto
     150      | SkidRow   |  Dave
     500      | Motorhead |  Lemmy

标签： sqlpostgresqlgreatest-n-per-group

Answer 1

使用distinct on：

select distinct on (band) t.*
from test t
order by band, random_number desc;

这是一个 db<>fiddle。

distinct on是一个非常方便的 Postgres 扩展。对于大型数据集的性能，您需要在(band, random_number desc).