By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE cats
(
name VARCHAR(10),
breed VARCHAR(25),
weight decimal(8,2),
color VARCHAR(25),
age int
);
INSERT INTO cats VALUES('Ashes','Persian',4.5,'Black',5);
INSERT INTO cats VALUES('Molly','Persian',4.2,'Black',1);
INSERT INTO cats VALUES('Felix','Persian',5.0,'Tortoiseshell',2);
INSERT INTO cats VALUES('Smudge','British Shorthair',4.9,'Black',4);
INSERT INTO cats VALUES('Tigger','British Shorthair',3.8,'Tortoiseshell',2);
INSERT INTO cats VALUES('Alfie','Siamese',5.5,'Brown',5);
INSERT INTO cats VALUES('Oscar','Siamese',6.1,'Black',1);
INSERT INTO cats VALUES('Millie','Maine Coon',5.4,'Tortoiseshell',5);
INSERT INTO cats VALUES('Misty','Maine Coon',5.7,'Brown',2);
INSERT INTO cats VALUES('Puss','Maine Coon',5.1,'Tortoiseshell',2);
INSERT INTO cats VALUES('Smokey','Maine Coon',6.1,'Brown',4);
INSERT INTO cats VALUES('Charlie','British Shorthair',4.8,'Black',4);
12 rows affected
select * from (
select *, row_number() over(partition by color order by age desc) rn from cats
) t where rn = 1
| name | breed | weight | color | age | rn |
|---|---|---|---|---|---|
| Ashes | Persian | 4.50 | Black | 5 | 1 |
| Alfie | Siamese | 5.50 | Brown | 5 | 1 |
| Millie | Maine Coon | 5.40 | Tortoiseshell | 5 | 1 |