How to write a WHERE clause with row_number in T-SQL?

Question

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!

Answer 1

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)