选择最近的记录 [英] selecting most recent record

问题描述

如果记录具有几乎相同类型的数据，如何选择最近的记录...

How to select most recent records if the records are having almost same kind of data...

示例:

col1       col2          col3              col4
--------------------------------------------------
123         abc          1.1               12345
123         abc          1.1               123445
1234        dsv          2.0               123
1234        dsv          2.0               1233
12345       dsvw         1.2               1234

col4 与其余列比较时的最大值.

The maximum of col4 when it compares with rest of the columns.

看到 row1 和 row2 的数据看起来很相似，但我们需要基于 col4 的最新数据.

See the row1 and row2 data looks similar but we need the latest data based on col4.

row3 和 row4 一样，数据看起来差不多，但我们需要基于 col4 的最新数据.

Same thing with row3 and row4, data looks similar but we need the latest data based on col4.

所需的输出是:

col1    col2   col3   col4
----------------------------
123     abc    1.1    123445
1234    dsv    2.0    1233
12345   dsvw   1.2    1234 

推荐答案

使用:

WITH example AS (
   SELECT t.*,
          ROW_NUMBER() OVER (PARTITION BY t.col1
                                 ORDER BY t.col4 DESC) AS rnk
     FROM YOUR_TABLE t)
SELECT e.*
  FROM example e
 WHERE e.rnk = 1

..或:

SELECT e.*
  FROM (SELECT t.*,
               ROW_NUMBER() OVER (PARTITION BY t.col1
                                      ORDER BY t.col4 DESC) AS rnk
          FROM YOUR_TABLE t) e
 WHERE e.rnk = 1

CTE 不提供对派生表方法的任何优化.

The CTE doesn't provide any optimization over the derived table approach.

这篇关于选择最近的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！