I prefer to use as little code as possible...

You can useINto achieve Try this:

SELECT * FROM t1 WHERE (id,rev) IN ( SELECT id, MAX(rev) FROM t1 GROUP BY id )

In my opinion, this is simpler... easier to read and maintain.

At first glance...

You only need to use theMAXaggregate function in theGROUP BYclause:

SELECT id, MAX(rev) FROM YourTable GROUP BY id

Things are never that simple, right?

I just noticed that you also need thecontentcolumn.

In SQL, this is a very common problem: find the entire row of data with the maximum value in a certain column based on a certain grouping identifier. I've heard this question a lot in my career. In fact, this is a question I answered during a technical interview for my current job.

This question is actually so common that the Stack Overflow community created a tag specifically to deal with this type of question:greatest-n-per-group.

Basically, you have two ways to solve this problem:

Use simplegroup-identifier, max-value-in-groupSubquery to connect

In this approach, you first find thegroup-identifier, max-value-in-group(already solved above) in a subquery. You then join your table with the subquery, usinggroup-identifierandmax-value-in-groupfor an equijoin:

SELECT a.id, a.rev, a.contents FROM YourTable a INNER JOIN ( SELECT id, MAX(rev) rev FROM YourTable GROUP BY id ) b ON a.id = b.id AND a.rev = b.rev

Use self-join for left join, and adjust the connection conditions and filtering conditions

In this approach, you left join the table to itself. Equivalent connections are placed ingroup-identifier. Then, there are two clever steps:

1. The second connection condition is that the value on the left is less than the value on the right
2. When you do step 1, the row that actually has the largest value will haveNULLon the right (remember this is aLEFT JOIN). We then filter the results of the join to only show rows withNULLon the right.

So, you end up with:

SELECT a.* FROM YourTable a LEFT OUTER JOIN YourTable b ON a.id = b.id AND a.rev < b.rev WHERE b.id IS NULL;

in conclusion

Both methods will give exactly the same results.

If there are two rows withmax-value-in-groupingroup-identifier, then these two rows will appear in the result in both methods.

Both methods are SQL ANSI compatible, so no matter what "flavor" of RDBMS you prefer, you can use it.

Both methods are also very friendly in terms of performance, but your actual situation may be different (RDBMS, database structure, index, etc.). Therefore,benchmarkwhen choosing a method. Make sure to choose the method that makes the most sense for you.