Question

I m 寻求一种手法,以书写SQ。

缩略语并且i必须查阅该表中的以下记录:,lname,accountid,最新日期以greatest(max(生育_date),max(update_date)(注:/code> 无效

我预计,我需要利用分析职能。

我有这些情况:

(id,fname,lname,accountid,creation_date,update_date)
(1, a , a , 2 , 07/01/2010 10:59:43 , 07/01/2010 10:59:43 )
(2, a , a , 2 , 07/01/2010 10:59:43 , 07/01/2010 10:59:43 )
(3, a , a , 2 , 07/01/2010 10:59:43 , 07/01/2010 10:59:43 )

我要选择最后一点:这一记录(3,a,a, 2 , 07/01/2010 10:59:43 , 07/01/2010 10:59:43 )

(id,fname,lname,accountid,creation_date,update_date)
(3, b , a , 2 , 07/01/2009 10:59:43 , 07/01/2010 10:59:43 )
(4, b , a , 2 , 07/01/2011 10:59:43 ,null)
(5, b , a , 2 , 07/01/2009 10:59:43 , 07/01/2009 10:59:43 )

我想选择两个栏上的最新一个栏目(编号:日期,日期:日期)(4,b, a, 2 , 07/01/201110:59:43,null)

(id,fname,lname,accountid,creation_date,update_date)
(6, c , g , 4 , 07/01/2010 10:59:43 ,null)
(7, c , g , 4 , 07/01/2011 10:59:43 ,null)
(8, c , g , 4 , 07/01/2009 10:59:43 ,null)

我想选择两个栏上的最新一栏(制作——日期,日期——日期),即(7,c, g, 4 , 07/01/2011 10:59:43,null)

(id,fname,lname,accountid,creation_date,update_date)
(9, k , t , 2 , 07/01/2009 10:59:43 , 07/01/2012 10:59:43 )
(10, k , t , 2 , 07/01/2011 10:59:43 ,null)
(11, k , t , 2 , 07/01/2009 10:59:43 , 07/01/2009 10:59:43 )

I want to choose the most recent one on both columns (creation_date,update_date) which is (9, k , t , 2 , 07/01/2009 10:59:43 , 07/01/2012 10:59:43 )

Answer 1

您应使用分析功能rel=“nofollow”rank(或 rel=“nofollow”><<<>>>>a>。我自己特别倾向于<代码>rank(<>>>>>,但只有采用独特的指数进行分割,才能真正行之有效。假设在<代码>上有一个独特的索引,名称、会计、制作——日期。

select *
  from ( select a.*
              , rank() over ( partition by fname, lname, accountid 
                      order by creation_date desc
                              , update_date desc ) as rnk
           from my_table a )
 where rnk = 1

每一组合的<代码>fname, lname, accountid,creation_date,update_。借助<代码>,空白= 1,然后由您选择<代码>的封顶编号——日期、最新情况_。

Answer 2

就像你想要的东西一样。

SELECT *
  FROM (SELECT a.*,
               rank() over (partition by fname, lname, accountid
                                order by coalesce( update_date, creation_date ) desc,
                                         id desc) rnk
          FROM your_table)
 WHERE rnk = 1

I m guessing a bit at why you would want the row with an id of 3 returned rather than the rows with an id of 1 or 2 since all 3 rows have the same update_date and creation_date. My guess is that you want to pick the tie by choosing the row with the largest id value. If you want to implement a different rule, you ll need to tell us what rule that is.
I am also assuming that the update_date, if it exists, will always be greater than or equal to the creation_date. It would be odd if a row were updated before it was created.

友情链接