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

Check out window functions: https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group — Dinu, Mar 24 '19 at 11:53

score 15 · Accepted Answer · answered Mar 24 '19 at 11:51

15

Use distinct on:

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

Here is a db<>fiddle.

distinct on is a very handy Postgres extension. For performance on large datasets, you want an index on (band, random_number desc).

answered Mar 24 '19 at 11:51

Gordon Linoff

1,242,037
58
646
786

The problem with this answer is that it orders the results based on the alphabetic order of the band name. In my case I wanted to have a DISTINCT name and at the same time results based on the descending random_number. I achieved it with nested SELECT: `select random_number, band from (select distinct on (band) t.* from test t order by band, random_number desc) as nested order by random_number desc;` [Here](https://dbfiddle.uk/?rdbms=postgres_10&fiddle=6296d47f8e0e5db9af33ed39c049ec5b) is the modified db<>fiddle – JacobTheKnitter Aug 09 '22 at 12:00

score 6 · Answer 2 · answered Mar 24 '19 at 11:53

6

Find the maximum random_number by grouping and join to the table:

select t.* 
from test t inner join (
  select band, max(random_number) maxnum from test group by band
) g
on g.band = t.band and g.maxnum = t.random_number

See the demo

answered Mar 24 '19 at 11:53

forpas

160,666
10
38
76

score -1 · Answer 3 · answered Mar 24 '19 at 11:47

-1

Use MAX()

SELECT max(random_number), band FROM test GROUP BY band

See: https://rextester.com/BEZD44968

Result would be:

no  max band
1   2800 Metallica
2   500 Motorhead
3   150 SkidRow

answered Mar 24 '19 at 11:47

xangr

879
14
28

thanks for your answer. I would like to see the `member_name` in the result. – JosepB Mar 24 '19 at 11:52
Ah, then use distinct. See @Gordon's answer. – xangr Mar 24 '19 at 11:53
When I try to test the code via the link you provided, I get: `"Error(s), warning(s): This language is for patrons only. Visit https://www.patreon.com/rextester for more information."` – JacobTheKnitter Aug 09 '22 at 10:34

Postgresql Get max value of each group with multiple columns

3 Answers3