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 |