0

My question is that I have certain table with some varchar2 values and insert date. What I want to do is to get latest two such entries grouped by this varchar2 value Is it possible to include some top(2) instead of max in Oracle group by ?

xwhyz
  • 1,444
  • 4
  • 21
  • 45
  • Or maybe there's at least possibility to run it one by one query for last entry and second last? – xwhyz Nov 28 '14 at 09:45
  • http://stackoverflow.com/questions/tagged/oracle+greatest-n-per-group –  Nov 28 '14 at 09:46

1 Answers1

2

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
MihaiC
  • 1,618
  • 1
  • 10
  • 14
  • and how to omit the case in which I might have two entries with the same date? then it displays both with rank 2 instead of either one entry or two with rank 1 – xwhyz Nov 28 '14 at 11:01