SQL Server 2014

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

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