SQL只选择在列上具有最大值的行。

I have this table for documents (simplified version here):

我有这张桌子的文件(简化版):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

How do I select one row per id and only the greatest rev?
With the above data, the result should contain two rows: [1, 3, ...] and [2, 1, ..]. I'm using MySQL.

我如何选择每个id的一行，并且只选择最伟大的rev?使用上述数据，结果应该包含两行:[1,3，…[2,1，..]。我使用的是MySQL。

Currently I use checks in the while loop to detect and over-write old revs from the resultset. But is this the only method to achieve the result? Isn't there a SQL solution?

目前，我在while循环中使用检查来检测和改写resultset中的旧的rev。但这是实现这个结果的唯一方法吗?没有SQL解决方案吗?

Update
As the answers suggest, there is a SQL solution, and here a sqlfiddle demo.

更新如答案所示，有一个SQL解决方案，这里有一个sqlfiddle演示。

Update 2
I noticed after adding the above sqlfiddle, the rate at which the question is upvoted has surpassed the upvote rate of the answers. That has not been the intention! The fiddle is based on the answers, especially the accepted answer.

更新2我注意到在添加了以上的sqlfiddle之后，问题的上升速度已经超过了答案的上升速度。这不是我的本意!小提琴是基于答案，尤其是公认的答案。

29 个解决方案

#1

At first glance...

All you need is a GROUP BY clause with the MAX aggregate function:

你所需要的是一个包含MAX聚合函数的GROUP BY子句:

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

It's never that simple, is it?

I just noticed you need the content column as well.

我注意到你也需要内容列。

This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that a lot during my career. Actually, it was one the questions I answered in my current job's technical interview.

在SQL中，这是一个非常常见的问题:在每个组标识符的列中，找到一行中有一些最大值的行的全部数据。在我的职业生涯中，我经常听到这样的话。事实上，这是我在目前的工作面试中回答的一个问题。

It is, actually, so common that * community has created a single tag just to deal with questions like that: greatest-n-per-group.

实际上，*社区已经创建了一个单一的标签来处理这样的问题:每个组都是最大的。

Basically, you have two approaches to solve that problem:

基本上，你有两种方法来解决这个问题:

Joining with simple group-identifier, max-value-in-group Sub-query

In this approach, you first find the group-identifier, max-value-in-group (already solved above) in a sub-query. Then you join your table to the sub-query with equality on both group-identifier and max-value-in-group:

在这种方法中，您首先会在子查询中找到group-identifier、max-value-in-group(已经解决了)。然后，将表与组标识符和max-value-in-组的相等的子查询连接起来:

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

Left Joining with self, tweaking join conditions and filters

In this approach, you left join the table with itself. Equality, of course, goes in the group-identifier. Then, 2 smart moves:

在这种方法中，您离开了与它自己的表。等式，当然，在组标识符中。然后,2智能移动:

1. The second join condition is having left side value less than right value
2. 第二个连接条件是左侧值小于正确值。
3. When you do step 1, the row(s) that actually have the max value will have NULL in the right side (it's a LEFT JOIN, remember?). Then, we filter the joined result, showing only the rows where the right side is NULL.
4. 当你执行第1步时，实际上有最大值的行(s)将在右边有NULL(这是一个左连接，记得吗?)然后，我们过滤连接的结果，只显示右边为空的行。

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;

Conclusion

Both approaches bring the exact same result.

两种方法都带来了同样的结果。

If you have two rows with max-value-in-group for group-identifier, both rows will be in the result in both approaches.

如果您有两行用于group-identifier的max-value-in-group，那么这两行都将在这两种方法的结果中。

Both approaches are SQL ANSI compatible, thus, will work with your favorite RDBMS, regardless of its "flavor".

这两种方法都是SQL ANSI兼容的，因此，不管它的“味道”如何，都可以使用您最喜欢的RDBMS。

Both approaches are also performance friendly, however your mileage may vary (RDBMS, DB Structure, Indexes, etc.). So when you pick one approach over the other, benchmark. And make sure you pick the one which make most of sense to you.

这两种方法都是性能友好的，但是您的里程可能有所不同(RDBMS、DB结构、索引等)。所以当你选择一种方法而不是另一种方法时，基准测试。一定要选对你最有意义的。

#2

My preference is to use as little code as possible...

我的首选是尽量少使用代码……

You can do it using IN try this:

你可以试试这个:

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

to my mind it is less complicated... easier to read and maintain.

在我看来，它不那么复杂……更容易阅读和维护。

#3

Yet another solution is to use a correlated subquery:

另一个解决方案是使用相关的子查询:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

Having an index on (id,rev) renders the subquery almost as a simple lookup...

有一个索引(id,rev)几乎是一个简单的查找…

Following are comparisons to the solutions in @AdrianCarneiro's answer (subquery, leftjoin), based on MySQL measurements with InnoDB table of ~1million records, group size being: 1-3.

以下是与@AdrianCarneiro的答案(subquery, leftjoin)的解决方案的比较，基于MySQL的测量数据，InnoDB表有100万的记录，组大小为:1-3。

While for full table scans subquery/leftjoin/correlated timings relate to each other as 6/8/9, when it comes to direct lookups or batch (id in (1,2,3)), subquery is much slower then the others (Due to rerunning the subquery). However I couldnt differentiate between leftjoin and correlated solutions in speed.

对于完整的表扫描，子查询/leftjoin/相关的时间间隔是6/8/9，当它涉及到直接查找或批处理时(在(1、2、3)中)，子查询比其他的要慢得多(因为重新运行子查询)。但是，我不能在速度上区分左连接和相关的解决方案。

One final note, as leftjoin creates n*(n+1)/2 joins in groups, its performance can be heavily affected by the size of groups...

最后一点，当leftjoin创建n*(n+1)/2加入组时，它的性能会受到组大小的严重影响……

#4

I can't vouch for the performance, but here's a trick inspired by the limitations of Microsoft Excel. It has some good features

我不能保证性能，但这是一个受微软Excel的限制启发的技巧。它有一些很好的特点。

GOOD STUFF

好东西

• It should force return of only one "max record" even if there is a tie (sometimes useful)
• 即使有一条领带(有时很有用)，它也应该强制只返回一个“最大记录”
• It doesn't require a join
• 它不需要连接。

APPROACH

方法

It is a little bit ugly and requires that you know something about the range of valid values of the rev column. Let us assume that we know the rev column is a number between 0.00 and 999 including decimals but that there will only ever be two digits to the right of the decimal point (e.g. 34.17 would be a valid value).

它有点难看，要求你知道rev列的有效值范围。假设我们知道rev列是0和999之间的一个数字，包括小数，但小数点右边只会有两位数字(例如，34.17是一个有效值)。

The gist of the thing is that you create a single synthetic column by string concatenating/packing the primary comparison field along with the data you want. In this way, you can force SQL's MAX() aggregate function to return all of the data (because it has been packed into a single column). Then you have to unpack the data.

问题的要点是，您可以通过字符串串联/打包主比较字段和您想要的数据来创建一个合成列。通过这种方式，您可以强制SQL的MAX()聚合函数返回所有数据(因为它已经被打包成一个列)。然后你必须打开数据。

Here's how it looks with the above example, written in SQL

下面是用SQL编写的上述示例的外观。

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

The packing begins by forcing the rev column to be a number of known character length regardless of the value of rev so that for example

包装开始时，无论rev的值是多少，都迫使rev列是一个已知的字符长度。

• 3.2 becomes 1003.201
• 3.2变成1003.201
• 57 becomes 1057.001
• 57变成了1057.001
• 923.88 becomes 1923.881
• 923.88变成1923.881

If you do it right, string comparison of two numbers should yield the same "max" as numeric comparison of the two numbers and it's easy to convert back to the original number using the substring function (which is available in one form or another pretty much everywhere).

如果你做对了，两个数字的字符串比较应该会产生相同的“max”作为两个数字的数字比较，并且很容易使用substring函数(在任何地方都可以使用)转换回原来的数字。

#5

I am flabbergasted that no answer offered SQL window function solution:

我很吃惊，没有任何答案提供SQL window函数解决方案:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

Added in SQL standard ANSI/ISO Standard SQL:2003 and later extended with ANSI/ISO Standard SQL:2008, window (or windowing) functions are available with all major vendors now. There are more types of rank functions available to deal with a tie issue: RANK, DENSE_RANK, PERSENT_RANK.

在SQL标准ANSI/ISO标准SQL中添加:2003年，后来又扩展了ANSI/ISO标准SQL:2008年，窗口(或窗口)功能现在可以和所有主要的供应商一起使用了。有更多类型的等级函数可用来处理tie问题:rank、DENSE_RANK、PERSENT_RANK。

#6

I think this is the easiest solution :

我认为这是最简单的解决方法:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;

• SELECT * : Return all fields.
• 选择*:返回所有字段。
• FROM Employee : Table searched on.
• 来自员工:表格搜索。
• (SELECT *...) subquery : Return all people, sorted by Salary.
• (选择*…)子查询:返回所有人，按工资排序。
• GROUP BY employeesub.Salary: : Force the top-sorted, Salary row of each employee to be the returned result.
• 由employeesub集团。工资::强制要求每名员工的工资排在最前面，并将其作为返回的结果。

If you happen to need just the one row, it's even easier :

如果你恰好需要一行，那就更简单了:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

I also think it's the easiest to break down, understand, and modify to other purposes:

我也认为它是最容易分解、理解和修改到其他目的的:

• ORDER BY Employee.Salary DESC: Order the results by the salary, with highest salaries first.
• 按员工。工资标准:按工资顺序排列结果，薪水最高。
• LIMIT 1: Return just one result.
• 限制1:只返回一个结果。

Understanding this approach, solving any of these similar problems becomes trivial: get employee with lowest salary (change DESC to ASC), get top-ten earning employees (change LIMIT 1 to LIMIT 10), sort by means of another field (change ORDER BY Employee.Salary to ORDER BY Employee.Commission), etc..

理解这种方法，解决这些类似的问题就变得微不足道了:获得最低工资的员工(改变DESC到ASC)，获得收入最高的10名员工(改变限制1到10)，通过另一个领域进行排序(由员工更改订单)。工资由雇员支付。佣金)等。

#7

Something like this?

是这样的吗?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

#8

Since this is most popular question with regard to this problem, I'll re-post another answer to it here as well:

既然这是关于这个问题的最普遍的问题，我将在这里重新发布另一个答案:

It looks like there is simpler way to do this (but only in MySQL):

看起来有更简单的方法(但仅限于MySQL):

select *
from (select * from mytable order by id, rev desc ) x
group by id

Please credit answer of user Bohemian in this question for providing such a concise and elegant answer to this problem.

请您在这个问题上给予用户波西米亚的信用回答，以便为这个问题提供一个简洁而优雅的答案。

EDIT: though this solution works for many people it may not be stable in the long run, since MySQL doesn't guarantee that GROUP BY statement will return meaningful values for columns not in GROUP BY list. So use this solution at your own risk

编辑:虽然这个解决方案对很多人有效，但从长远来看它可能不稳定，因为MySQL不能保证GROUP BY语句将返回有意义的值，而不是列表中的列。所以，在你自己的风险中使用这个解决方案。

#9

A third solution I hardly ever see mentioned is MySQL specific and looks like this:

我几乎没有提到的第三个解决方案是MySQL特有的，它是这样的:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

Yes it looks awful (converting to string and back etc.) but in my experience it's usually faster than the other solutions. Maybe that just for my use cases, but I have used it on tables with millions of records and many unique ids. Maybe it's because MySQL is pretty bad at optimizing the other solutions (at least in the 5.0 days when I came up with this solution).

是的，它看起来很糟糕(转换为字符串和返回等)，但在我的经验中，它通常比其他的解决方案快。也许这只是针对我的用例，但我已经在有数百万条记录和许多惟一id的表上使用了它。也许是因为MySQL在优化其他解决方案方面非常糟糕(至少在我提出这个解决方案的5.0天内)。

One important thing is that GROUP_CONCAT has a maximum length for the string it can build up. You probably want to raise this limit by setting the group_concat_max_len variable. And keep in mind that this will be a limit on scaling if you have a large number of rows.

一件重要的事情是GROUP_CONCAT拥有可以构建的字符串的最大长度。您可能希望通过设置group_concat_max_len变量来提高这个限制。记住，如果有大量的行，这将是缩放的限制。

Anyway, the above doesn't directly work if your content field is already text. In that case you probably want to use a different separator, like \0 maybe. You'll also run into the group_concat_max_len limit quicker.

不管怎样，如果你的内容字段已经是文本了，以上这些都不会直接起作用。在这种情况下，您可能需要使用不同的分隔符，比如\0。您还会更快地遇到group_concat_max_len限制。

#10

I like to use a NOT EXIST-based solution for this problem:

我喜欢用一个不存在的解决方案来解决这个问题:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

#11

How about this:

这个怎么样:

select all_fields.*  
from  (select id, MAX(rev) from yourtable group by id) as max_recs  
left outer join yourtable as all_fields  
on max_recs.id = all_fields.id

#12

I would use this:

我想用这个:

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

Subquery SELECT is not too eficient maybe, but in JOIN clause seems to be usable. I'm not an expert in optimizing queries, but I've tried at MySQL, PostgreSQL, FireBird and it does work very good.

子查询选择不太可能，但是在JOIN子句中似乎是可用的。我不是优化查询的专家，但我在MySQL、PostgreSQL、FireBird上尝试过，而且效果非常好。

You can use this schema in multiple joins and with WHERE clause. It is my working example (solving identical to yours problem with table "firmy"):

您可以在多个连接和WHERE子句中使用此模式。这是我的工作范例(解决与你的问题同表“firmy”):

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

It is asked on tables having teens thusands of records, and it takes less then 0,01 second on really not too strong machine.

它被要求在有记录的青少年的桌子上，并且它在不太强大的机器上花费的时间是0,01秒。

I wouldn't use IN clause (as it is mentioned somewhere above). IN is given to use with short lists of constans, and not as to be the query filter built on subquery. It is because subquery in IN is performed for every scanned record which can made query taking very loooong time.

我不会在从句中使用(就像上面提到的那样)。在给定的情况下，可以使用简短的constans列表，而不是构建在子查询上的查询过滤器。这是因为在每个扫描记录中执行的子查询可以使查询变得非常的轻松。

#13

If you have many fields in select statement and you want latest value for all of those fields through optimized code:

如果您在select语句中有很多字段，并且您希望通过优化代码来获得所有这些字段的最新值:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

#14

This solution makes only one selection from YourTable, therefore it's faster. It works only for MySQL and SQLite(for SQLite remove DESC) according to test on sqlfiddle.com. Maybe it can be tweaked to work on other languages which I am not familiar with.

这个解决方案只从您的表中做出一个选择，因此它更快。根据sqlfiddle.com上的测试，它只适用于MySQL和SQLite(用于SQLite删除DESC)。也许它可以调整到其他我不熟悉的语言。

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id

#15

NOT mySQL, but for other people finding this question and using SQL, another way to resolve the greatest-n-per-group problem is using Cross Apply in MS SQL

不是mySQL，但是对于其他发现这个问题和使用SQL的人来说，另一种解决最严重的问题的方法是在MS SQL中使用交叉应用。

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

Here's an example in SqlFiddle

#16

Here is a nice way of doing that

这里有一个很好的方法。

Use following code :

使用以下代码:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)

#17

I like to do this by ranking the records by some column. In this case, rank rev values grouped by id. Those with higher rev will have lower rankings. So highest rev will have ranking of 1.

我喜欢通过在某个列中对记录进行排序。在本例中，按id分组的rev值，具有较高rev的值将具有较低的排名。所以最高的rev的排名是1。

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

Not sure if introducing variables makes the whole thing slower. But at least I'm not querying YOURTABLE twice.

不确定引入变量是否会使整个过程变慢。但至少我不是在查询你的表两次。

#18

If anyone is looking for a Linq verson, this seems to work for me:

如果有人想找一个Linq verson，这对我来说似乎很有用:

public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
    var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
        .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );    

    return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}

#19

here is another solution hope it will help someone

这是另一个解决方案，希望它能帮助到别人。

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev

#20

None of these answers have worked for me.

这些答案都不适合我。

This is what worked for me.

这就是我的工作。

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

#21

SELECT * FROM Employee where Employee.Salary in (select max(salary) from Employee group by Employe_id) ORDER BY Employee.Salary

从雇员那里选择*。工资(根据雇员的要求从雇员组中选择最高工资)。

#22

Here's another solution to retrieving the records only with a field that has the maximum value for that field. This works for SQL400 which is the platform I work on. In this example, the records with the maximum value in field FIELD5 will be retrieved by the following SQL statement.

这里有另一种方法，仅用具有该字段最大值的字段来检索记录。这适用于SQL400，这是我工作的平台。在本例中，将通过以下SQL语句检索字段FIELD5中最大值的记录。

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)

#23

Many, if not all, of the other answers here are fine for small datasets. For scaling, more care is needed. See here.

许多，如果不是全部的话，这里的其他答案对于小数据集来说是可以接受的。对于扩展，需要更多的关注。在这里看到的。

It discusses multiple faster ways to do groupwise max and top-N per group.

它讨论了多个更快的方法来实现groupwise max和top-N /组。

#24

Sorted the rev field in reverse order and then grouped by id which gave the first row of each grouping which is the one with the highest rev value.

将rev字段按相反顺序排序，然后按id进行分组，该id为每个分组的第一行提供了最高的rev值。

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

Tested in http://sqlfiddle.com/ with the following data

在http://sqlfiddle.com/中测试以下数据。

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

This gave the following result in MySql 5.5 and 5.6

这在MySql 5.5和5.6中给出了如下结果。

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two

#25

I used the below to solve a problem of my own. I first created a temp table and inserted the max rev value per unique id.

我用下面的方法来解决我自己的问题。我首先创建了一个临时表，并插入了每个惟一id的最大rev值。

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

I then joined these max values (#temp1) to all of the possible id/content combinations. By doing this, I naturally filter out the non-maximum id/content combinations, and am left with the only max rev values for each.

然后，我将这些最大值(#temp1)加入到所有可能的id/内容组合中。通过这样做，我可以很自然地过滤掉非最大的id/内容组合，并且只剩下每个值的最大rev值。

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

#26

Another manner to do the job is using MAX() analytic function in OVER PARTITION clause

另一种方法是使用MAX()分析函数在分区子句中。

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

The other OVER PARTITION solution already documented in this post is

在本文中已经记录的另一个分区解决方案是。

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
      FROM YourTable
    ) t
  WHERE t.rank = 1 

This 2 SELECT work well on Oracle 10g.

这个2选择在Oracle 10g上很好。

#27

select * from yourtable
group by id
having rev=max(rev);

#28

This works for me in sqlite3:

这在sqlite3中对我有效:

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

With *, you get a duplicate rev column, but that's not much of a problem.

使用*，你会得到一个重复的rev列，但这并不是一个很大的问题。

#29

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;