By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table carSales (
car varchar(25),
colour varchar(10),
sold int);
insert into carSales values
('Benz','Black', 2),
('Benz','White', 1),
('BMW', 'Black', 3),
('BMW', 'Green' , 2),
('BMW', 'Blue', 1),
('Toyota','Yellow', 7),
('Toyota','Red', 5),
('Toyota','Blue', 3),
('Toyota','White', 1);
with rankedSales as (
select
car,
colour,
sold,
rank() over (partition by car order by sold desc) rn
from carSales)
select
car,
colour,
sold
from rankedSales
where rn = 1;
| car | colour | sold |
|---|---|---|
| Benz | Black | 2 |
| BMW | Black | 3 |
| Toyota | Yellow | 7 |