How to use the WHERE clause on top of a column made from row_number() over()

Question

I’m trying to use Where on top of a column generated by row_number but it doesn’t work; it’s possible to do this?

Answer 1

Yes, one way is through the use of CTE.

-- código #1
with Sequenciado as (
SELECT *,
       seq= row_number() over (partition by colA order by colB)
  from tabA
)
SELECT seq, colA, colB
  from Sequenciado
  where seq between 1 and 5;

The above code uses CTE (common table Expression), which makes it easier to understand and maintain it. See article "Modular programming with table expressions (CTE)”.

Answer 2

You can also use a sub-base:

SELECT seq, 
       colA, 
       colB
FROM
    (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY colA ORDER BY colB) AS Seq
        FROM tabA
    ) AS T
WHERE T.Seq BETWEEN 1 AND 5;