Trying to join tables and select by one distinct max value

Question

Hi, I am trying to pull multiple columns from 3 joined tables, but I want the result set to contain only one distinct "data entry" per p.id (the pet id which is a foreign key in the data entry table). The issue i have is that there could be two data entries, numbered 1 and 2, belonging to a pet - the query has to just pick the data entry with the highest number - that's why i was trying to use max and group by but it doesn't quite work. Can anyone see where i'm going wrong? Many thanks

SELECT `p`.`id`, `o`.`id`, `o`.`email`, MAX(d.number), `d`.`number`
FROM (`pets` AS `p`, `owners` AS `o`, `data_entries` AS `d`) 
WHERE `p`.`owner_id` = `o`.`id` 
AND `p`.`id` = `d`.`pet_id` 
GROUP BY `p`.`id` 
ORDER BY `d`.`number` DESC

EDIT: following iniju's suggestion, I tried the following query, but this doesn't not filter out the data entries where the number is lower than another data entry for the same pet :

SELECT `p`.`id`, `o`.`id`, `o`.`email`, `d`.`number`
FROM (`pets` AS `p`, `owners` AS `o`, `data_entries` AS `d`) 
WHERE `p`.`owner_id` = `o`.`id` 
AND `p`.`id` = `d`.`pet_id` 
GROUP BY `p`.`id`, `o`.`id`, `o`.`email`, `d`.`number`
HAVING `d`.`number`=MAX(`d`.`number`)

Answer 1

If I'm understanding your question correctly, you need

LIMIT 1

at the end.

Answer 2

Your GROUP BY should contain all the non-aggregated columns that is:
GROUP BY 'p'.'id', 'o'.'id', 'o'.'email', 'd'.'number'

Full query:

SELECT `p`.`id`, `o`.`id`, `o`.`email`, MAX(d.number)  
FROM (`pets` AS `p`, `owners` AS `o`, `data_entries` AS `d`)  
WHERE `p`.`owner_id` = `o`.`id`  
    AND `p`.`id` = `d`.`pet_id`  
GROUP BY `p`.`id`, `o`.`id`, `o`.`email`  
ORDER BY `d`.`number` DESC 

Answer 3

You cannot simultaneously select both the number and the MAX number while grouping. This is because the MAX function is based on the aggregate of your grouping. In your case you are also selecting the column itself, which will create a row for each distinct number... not each distinct MAX number.

Let's say you have 4 rows:

PET    NUMBER
A      1
A      2
B      4
B      2

Do you want your result set to be:

PET  MAX
A    2
B    4

Or do you want it to be:

PET  NUM  MAX
A    1    2
A    2    2
B    4    4
B    2    4

The difference is that one is grouped and aggregated while the other is NOT grouped but contains the aggregated column.

I suggest you remove the d.number from your SELECT statement, leaving only MAX(d.number) This will solve your problem if, and only if, the o.id and o.email are unique to the joined record for p.id

Try this:

SELECT `p`.`id`, `o`.`id`, `o`.`email`, MAX(d.number)
FROM `data_entries` AS `d`
JOIN `pets` `p` ON `p`.`ID` = `d`.`pet_id`
JOIN `owners` `o` ON `o`.`ID` = `p`.`owner_id`
GROUP BY  `p`.`id`, `o`.`id`, `o`.`email`
ORDER BY MAX(d.number) DESC