WITH CTE AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY opened_at) RN
FROM
table
)
SELECT column1, column2
FROM CTE
WHERE RN = 2
If there is no RN = 2
, how I can return RN = 1
instead?
If I code something like
WHERE RN = IIF (RN = 2,2,1)
it will return both RN = 1
and RN = 2
for the rows that have 1 and 2. I only need RN = 2
, or RN = 1
, if RN = 2
does not exist.
Any suggestions would be highly appreciated.
Thank you!
You can count rows in partition by Id. If there 1 row (count=1) then rn=1 - nothing to do. If count>1 then take RN=2.
Try this
WITH CTE AS (
SELECT *, ROW_NUMBER () OVER (PARTITION BY id ORDER BY opened_at) RN
, count (*) OVER (PARTITION BY id) qty
FROM table)
SELECT column1, column2
FROM CTE
WHERE (qty>1 and RN = 2) or (qty=1)