从每个嵌套的分组记录中选择第一个记录 [英] Selecting the first record out of each nested grouped record

问题描述

我建立了一个查询(在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.

这篇关于从每个嵌套的分组记录中选择第一个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！