记录最大日期 [英] Taking the record with the max date

问题描述

假设我提取了一些数据.

即

SELECT A, date
FROM table

我只想要具有最大日期的记录(对于A的每个值).我可以写

SELECT A, col_date
  FROM TABLENAME t_ext
 WHERE col_date = (SELECT MAX (col_date)
                     FROM TABLENAME t_in
                    WHERE t_in.A = t_ext.A)

但是我的查询确实很长...使用ANALYTIC FUNCTION可以做得更紧凑吗?

解决方案

解析函数方法看起来像

SELECT a, some_date_column
  FROM (SELECT a,
               some_date_column,
               rank() over (partition by a order by some_date_column desc) rnk
          FROM tablename)
 WHERE rnk = 1

请注意，根据您要如何处理关系(或关系在数据模型中是否可能)，您可能要使用ROW_NUMBER或DENSE_RANK分析函数，而不是RANK.

Let's assume I extract some set of data.

i.e.

SELECT A, date
FROM table

I want just the record with the max date (for each value of A). I could write

SELECT A, col_date
  FROM TABLENAME t_ext
 WHERE col_date = (SELECT MAX (col_date)
                     FROM TABLENAME t_in
                    WHERE t_in.A = t_ext.A)

But my query is really long... is there a more compact way using ANALYTIC FUNCTION to do the same?

解决方案

The analytic function approach would look something like

SELECT a, some_date_column
  FROM (SELECT a,
               some_date_column,
               rank() over (partition by a order by some_date_column desc) rnk
          FROM tablename)
 WHERE rnk = 1

Note that depending on how you want to handle ties (or whether ties are possible in your data model), you may want to use either the ROW_NUMBER or the DENSE_RANK analytic function rather than RANK.

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