title: Select highest record of type per foreign key
body: <p>I am totally stumped on what seems like a simple problem. Maybe I'm just confused because I've been thinking/googling/searching around about it for too long.</p>
  
  <p>Consider the following table:</p>
  
  <pre><code>log_id (auto increment primary key)
  domain_id (foreign key to "domains" table)
  type (enum('notice','warning'))
  message (varchar)
  </code></pre>
  
  <p>Executing the following select:</p>
  
  <pre><code>SELECT *
  FROM logs
  WHERE domain_id = 4
  </code></pre>
  
  <p>Would give me, say, 2 logs of the type 'notice' and 3 of the type 'warning'. </p>
  
  <p>How would I go about selecting only the logs with the highest ID of their <code>type</code>? Effectively giving me the latest log per <code>domain_id</code> and <code>type</code>.</p>
  
  <p>Just describing it makes me feel stupid, but I can't seem to get it right..</p>
  
  <p>Thanks for your help.</p>
  
  <p>Chris</p>
  
  <p><strong>Edit:</strong>
  For future reference, in case you want to select all records from the first table and show the latest record of the joined table (in this case select all domains and their latest log record (if available)), simply wrap a select on the domains table around it:</p>
  
  <pre><code>SELECT 
    domains.*, 
    logs.*
  FROM 
    domains
  LEFT JOIN (
    SELECT 
      l.*
    FROM 
      logs l
    INNER JOIN (
      SELECT
        MAX(log_id) as maxid
      FROM
        logs
      GROUP BY
        domain_id
        type
    ) l3 ON l.log_id = l3.maxid
  ) l2 USING (domain_id)
  </code></pre>