-2

I have a DB2 table having one of the columns (A) which has either value PQR or XYZ.

I need output where the latest two records based on col C date have value A = PQR.

Sample Table

A   B     C
--- ----- ----------
PQR Mark  08/08/2019
PQR Mark  08/01/2019
XYZ Mark  07/01/2019
PQR Joe   10/11/2019
XYZ Joe   10/01/2019
PQR Craig 06/06/2019
PQR Craig 06/20/2019

In this sample table, my output would be Mark and Craig records

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
sam243475
  • 3
  • 2

2 Answers2

1

Since 11.1

You may use the nth_value OLAP function. Refer to OLAP specification.

SELECT A, B, C
FROM
(
SELECT 
  A, B, C
, NTH_VALUE (A, 1) OVER (PARTITION BY B ORDER BY C DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) C1
, NTH_VALUE (A, 2) OVER (PARTITION BY B ORDER BY C DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) C2
FROM TAB
)
WHERE C1 = 'PQR' AND C2 = 'PQR'

dbfiddle link.

Older versions

SELECT T.*
FROM TAB T
JOIN 
(
SELECT B
FROM
(
SELECT 
  A, B
, ROWNUMBER() OVER (PARTITION BY B ORDER BY C DESC) RN
FROM TAB
)
WHERE RN IN (1, 2)
GROUP BY B
HAVING MIN(A) = MAX(A) AND COUNT(1) = 2 AND MIN(A) = 'PQR'
) G ON G.B = T.B;
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
0

A simple solution could be

SELECT A,B,C 
  FROM tab
 WHERE A = 'PQR'
 ORDER BY C DESC FETCH FIRST 2 ROWS only
MichaelTiefenbacher
  • 3,805
  • 2
  • 11
  • 17