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

<pre><code>1|1|hallo1|
1|2|hallo2|
1|3|hallo3|
2|1|hallo1|
2|2|hallo2|
</code></pre>

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

<pre><code>1|3|hallo3|
2|2|hallo2|
</code></pre>

<p>I've come up with this solution:</p>

<pre><code>select id, revision, comment 
  from comments 
  where revision = (
      select max(revision) 
        from comments as f 
        where f.id = comments.id
  );
</code></pre>

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

Make sure you have your indexes set up appropriately.  Indexing on id, revision would be good.
Here's a different take on your query. Haven't checked its execution plan, but if you set up the index well it should help:
```
```
SELECT c.* 
  FROM comments c
  INNER JOIN (
        SELECT id,max(revision) AS maxrev 
          FROM comments 
          GROUP BY id
  ) b
    ON c.id=b.id AND c.revision=b.maxrev
```
```
Editted to add:
If you're on SQL Server, you might want to check out Indexed Views as well:
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
Editted again to add info:
```
```
Subquery:
25157 records
2 seconds
Execution plan includes an Index Seek (82%) base and a Segment (17%)

Left Outer Join:
25160 records
3 seconds
Execution plan includes two Index Scans @ 22% each with a Right Outer Merge at 45% and a Filter at 11%
```
```
I'd still go with the sub query.