sql - Best-performance query for "select max in group"?

Question

I have a simple table comments (id INT, revision INT, comment VARCHAR(140)) with some content like this:

1|1|hallo1|
1|2|hallo2|
1|3|hallo3|
2|1|hallo1|
2|2|hallo2|

I'm searching for an SQL statement which will return each comment with the highest revision:

1|3|hallo3|
2|2|hallo2|

I've come up with this solution:

select id, revision, comment 
  from comments 
  where revision = (
      select max(revision) 
        from comments as f 
        where f.id = comments.id
  );

but it is very slow on large data sets. Are there any better queries to accomplish this?

Answer 1

Here's one way that with appropriate indexing will not be heinously slow and it doesn't use a subselect:

SELECT comments.ID, comments.revision, comments.comment FROM comments 
LEFT OUTER JOIN comments AS maxcomments 
ON maxcomments.ID= comments.ID
AND maxcomments.revision > comments.revision
WHERE maxcomments.revision IS NULL

Adapted from queries here: http://www.xaprb.com/blog/2007/03/14/how-to-find-the-max-row-per-group-in-sql-without-subqueries/

(From google search: max group by sql)