Choose the highest type record for foreign key

Question

I am completely obsessed with what seems like a simple problem. Maybe I'm just embarrassed because I thought / searched / searched around for too long.

Consider the following table:

log_id (auto increment primary key) domain_id (foreign key to "domains" table) type (enum('notice','warning')) message (varchar) 

Making the following selection:

 SELECT * FROM logs WHERE domain_id = 4 

Would give me, say, 2 logs of the type "notification" and 3 types of "warning".

How can I select only the logs with the highest identifier of their type ? Effectively giving me the last log for domain_id and type .

Just describing it, I feel stupid, but I can’t understand that it’s right.

Thank you for your help.

Chris

Edit: For future reference, if you want to select all the records from the first table and show the last record of the joined table (in this case, select all the domains and their last log entry (if any)), simply wrap the table in the domains around it:

 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) 

Answer 1

First join the subquery:

 select l.* from log l inner join ( select max(log_id) as maxid from log group by domain_id, type ) l2 on l.log_id = l2.maxid 

Answer 2

What you need is GROUP BY, as this request is below:

 SELECT `type`, MAX(`log_id`) AS `latest_log` FROM `logs` WHERE `domain_id` = 4 GROUP BY `type`; 

Answer 3

And for a change (although I would use the @Eric answer) you can use EXISTS ...

 SELECT * FROM log WHERE NOT EXISTS ( SELECT * FROM log AS lookup WHERE domain_id = log.domain_id AND type = log.type AND log_id > log.log_id )