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
?
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