title: Trying to join tables and select by one distinct max value
body: <p>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 <code>p</code>.<code>id</code> (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</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 :</p>
  
  <pre><code>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`)
  </code></pre>