Col Col - 5 months ago 8
SQL Question

Select the nth most recent row from multiple groups

Using Oracle 12c, I have a table

table1
like this:

ID DATA1 DATA2 LAST_UPDATE_TIMESTAMP
1 1 2 time_stamp1
2 1 2 time_stamp2
3 2 1 time_stamp3
4 2 2 time_stamp4
5 1 2 time_stamp5
6 1 1 time_stamp6
7 2 2 time_stamp7
8 1 1 time_stamp8
9 2 1 time_stamp9
10 1 2 time_stamp10


The
DATA1
AND
DATA2
only has four posssible pairs:

1,1
1,2
2,1
2,2


How to get the IDs of every pair, if ordered by
LAST_UPDATE_TIMESTAMP
, which are the nth most recent records?


For example, if
LAST_UPDATE_TIMESTAMP
is already ordered in descending order, then for the most recent, the IDs of four pairs would be
1,3,4,6
. For the second most recent, it would be
2,7,8,9
.

Say
SELECT data1, data2 FROM table2
will return those four pair.

Answer

Try:

SELECT ID, DATA1, DATA2, LAST_UPDATE_TIMESTAMP,
       rn -- this is a number of pair: 1-first most recent, 2-second most recent etc.
FROM (
   SELECT t.*,
          row_number() 
             over (partition by data1, data2 
                   ORDER BY last_updated_timestamp DESC) as Rn
)
WHERE rn <= 5 -- where 5 is a limit ==> you will get at most 5 most recent records for each pair