且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

如何在SQL中按日期获取具有特定值的最新两行

更新时间:2023-01-20 17:35:39

EDIT Updated to not count duplicate date value for the same varchar2.

Replaced RANK() with DENSE_RANK() such that it assigns consecutive ranks, then used distinct to eliminate the duplicates.

You can use DENSE_RANK()

SELECT DISTINCT TXT, ENTRY_DATE
  FROM (SELECT txt,
               entry_date,
               DENSE_RANK () OVER (PARTITION BY txt ORDER BY entry_date DESC)
                  AS myRank
          FROM tmp_txt) Q1
 WHERE Q1.MYRANK < 3
ORDER BY txt, entry_date DESC

Input:

txt | entry_date

xyz | 03/11/2014
xyz | 25/11/2014
abc | 19/11/2014
abc | 04/11/2014
xyz | 20/11/2014
abc | 02/11/2014
abc | 28/11/2014
xyz | 25/11/2014
abc | 28/11/2014

Result:

txt | entry_date

abc | 28/11/2014
abc | 19/11/2014
xyz | 25/11/2014
xyz | 20/11/2014

相关阅读

技术问答最新文章