Missing row when using mysql max function-CodePudding

I have the following table below. I'm trying to retrieve data and group them by name, I then want only the latest row according to the created_at column. I ran the query below and seem to get getting what I want. However, when I add more weights, the query sometimes omits the some data. What is going on???

 "id"   "name"                  "value" "created_at"    "animal_id"
    "6"     "body_condition_score"  "9" "2022-02-26"       "1"
    "9"     "body_condition_score"  "8" "2022-02-27"      "1"
    "7"     "dental_score"          "4" "2022-02-25"       "1"
    "10"    "dental_score"          "1" "2022-03-25"       "1"
    "11"    "dental_score"          "5" "2022-03-25"       "2"
    "4"     "heart_rate"           "1500""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"



    SELECT vitals.* 
FROM    vitals
INNER JOIN  
( 
    SELECT `name`, `value`, MAX(created_at) created_at
    FROM vitals
    GROUP BY `name`
) grouped 
ON grouped.`name` = vitals.`name` 
AND grouped.created_at = vitals.created_at  
WHERE animal_id = 1

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

CodePudding user response：

whats the value of animal_id for your last data? if you add "weight" for animal_id = 2 and have the max created_at then your query will return no row for "weight"

because:

in subquery "grouped" not use "when animal_id = 1" then it is possible query return max created_at in animal_id = 2
you use inner join and "when animal_id = 1" on main query, so when sub query return created_at with animal_id = 2 they will not link.

my suggest: if you want to select only animal_id = 1, change your sub query to

INNER JOIN  
( 
    SELECT `name`, `value`, MAX(created_at) created_at
    FROM vitals
    WHERE animal_id = 1
    GROUP BY `name`
) grouped

if you does not care about animal_id, remove when animal_id = 1

SELECT vitals.* 
FROM    vitals
INNER JOIN  
( 
    SELECT `name`, `value`, MAX(created_at) created_at
    FROM vitals
    GROUP BY `name`
) grouped 
ON grouped.`name` = vitals.`name` 
AND grouped.created_at = vitals.created_at

CodePudding user response：

If you have MySQL 8 version you could use:

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;

If you have older version which doesn't support windows function try:

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;

Demo