从每个嵌套的分组记录中选择第一个记录

本文介绍了从每个嵌套的分组记录中选择第一个记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我建立了一个查询(在Oracle中)，该查询从重复的行中选择具有最大日期的任何行.我的查询基于此处，该查询使用嵌套分组:

I've built a query (in Oracle) that selects any row with the max date out of duplicated rows. I based my query on the one presented here, which uses a nested grouping:

SELECT * FROM ( SELECT Train, MAX(Time) as MaxTime FROM TrainTable GROUP BY Train ) r INNER JOIN TrainTable t ON t.Train = r.Train AND t.Time = r.MaxTime

现在，因为该查询不考虑Time中的重复值(如注释 )，我想从每个重复的"分组记录中取出第一记录，并仍然能够使用select * .

Now, since that query doesn't account for duplicated values in Time (as commented there), I'd like to take the first record out of each "duplicated" grouped record, and to still be able to use select *.

我该怎么办?

(P.S.我尝试使用其他解决方案(使用over (partition ...))，但没有用，我需要弄清楚)

(P.S. I tried using the other solution (using over (partition ...)), but it didn't work, and I'd need to figure it out)

推荐答案

使用row_number():

select t.* from (select t.*, row_number() over (partition by train order by time desc) as seqnum from traintable t ) t where seqnum = 1;

当匹配的time有联系时，这将返回任意行. SQL表表示无序集，因此没有第一"行，除非另一列指定了该顺序.如果是这样，则可以将该列包括在order by子句中.

This returns an arbitrary row when there are ties for the matching time. SQL tables represent unordered sets, so there is no "first" row, unless another column specifies that ordering. If so, then you can include that column in the order by clause.