Using Max() with other field with GROUP BY

Question

I have the following table in SQL SERVER:

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)

I need for each (color) to select\view the oldest cat, and it's name. It's very easy to use GROUP BY color but the problem is when you add the name everything is missed up.

I think I should use OVER(), Partition BY or RANK()/DENSE_RANK... I tried them all but I got stuck. please help me with this.

Thanks !

Answer 1

here is one way:

select * from (
   select *, row_number() over(partition by color order by age desc) rn from cats
) t where rn = 1

db<>fiddle here

Answer 2

Another way, using a correlated subquery:

Fiddle at http://sqlfiddle.com/#!18/798bd7/3.

select 
  (select top 1 name from cats c 
    where c.age = tmp.maxage 
          and c.color = tmp.color) name,
  color, 
  maxage
from (
  select color, max(age) as maxage
  from cats
  group by color
) tmp

Answer 3

SELECT DISTINCT
    color
    , [max] = max(age) over (partition by color)
FROM cats