MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


create table vitals (
id int,
name varchar(150),
`value` decimal(4.2) ,
created_at date ,
animal_id int );


insert into vitals values
(6,'body_condition_score',9.00,'2022-02-26',1),
(9,'body_condition_score',8.00,'2022-02-27',1),
(7,'dental_score',4.00,'2022-02-25',1),
(10,'dental_score',1.00,'2022-03-25',1),
(11,'dental_score',5.00,'2022-03-25',2),
(4,'heart_rate',1500.00,'2022-02-25',1),
(5,'respiratory_rate',20,'2022-02-25',1),
(2,'weight',90.52,'2022-02-22',1),
(3,'weight',99,'2022-02-23',1),
(13,'weight',0,'2022-02-21',1),
(12,'weight',1,'2022-02-25',2);
with cte as (
             select *,ROW_NUMBER() OVER ( partition by name ORDER BY created_at desc ) row_num
             from vitals where animal_id=1
)
select id,name,value,created_at,animal_id 
from cte 
where row_num=1;


id
name
value
created_at
animal_id


9
body_condition_score
8
2022-02-27
1

10
dental_score
1
2022-03-25
1

4
heart_rate
1500
2022-02-25
1

5
respiratory_rate
20
2022-02-25
1

3
weight
99
2022-02-23
1


SELECT v.id,v.name,v.value,v.created_at,v.animal_id
FROM vitals v
INNER JOIN (
    SELECT name,animal_id, MAX(created_at) as created_at
    FROM vitals
    where animal_id=1
    GROUP BY name,animal_id
           ) v1 ON v.name = v1.name AND v.created_at = v1.created_at
where v.animal_id=1;           


id
name
value
created_at
animal_id


9
body_condition_score
8
2022-02-27
1

10
dental_score
1
2022-03-25
1

4
heart_rate
1500
2022-02-25
1

5
respiratory_rate
20
2022-02-25
1

3
weight
99
2022-02-23
1

id	name	value	created_at	animal_id
9	body_condition_score	8	2022-02-27	1
10	dental_score	1	2022-03-25	1
4	heart_rate	1500	2022-02-25	1
5	respiratory_rate	20	2022-02-25	1
3	weight	99	2022-02-23	1