sqlt-sqlwhere-clausessmsrow-number

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


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!


Solution

  • 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)