Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE table test
(
    random_number INT,
    band VARCHAR(255),
    member_name VARCHAR(255)
);

CREATE TABLE

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');

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

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

random_number
band
member_name

2800
Metallica
Roberto

500
Motorhead
Lemmy

150
SkidRow
Dave

SELECT 3