Referencing ROW_NUMBER() in WHERE

Question

Having a query that results in over 500K results. What I want to do is split these up by 100K results. I created a row number column to help with that, but now that I'm referencing it in my WHERE it will not process the function and keep getting this error:

Msg 4108, Level 15, State 1, Line 1 Windowed functions can only appear in the SELECT or ORDER BY clauses.

Query for reference:

1  SELECT
2  mt.Name,
3  mt.IDNO,
4  so.IDType,
5  Row   =  ROW_NUMBER()OVER(ORDER BY mt.Name)
6
7  FROM         MainTable       mt WITH(NOLOCK)
8  LEFT JOIN    SupportTable1   so WITH(NOLOCK) ON  so.Name    = mt.Name
9  LEFT JOIN    SupportTable2   st WITH(NOLOCK) ON  st.Name    = mt.Name
10
11 WHERE    1=1
12 AND      ROW_NUMBER()OVER(ORDER BY mt.Name) BETWEEN '1' and '100000'

Msg 4108, Level 15, State 1, Line 12 Windowed functions can only
appear in the SELECT or ORDER BY clauses.

What can I do to either use this or is there another option to explore that can give me what I need?

Thanks.

Answer 1

You are not using MySQL. In order to do this, use a CTE or subquery:

SELECT s.*
FROM (SELECT mt.Name, mt.IDNO, so.IDType, Row = ROW_NUMBER() OVER (ORDER BY mt.Name)
      FROM MainTable mt LEFT JOIN
           SupportTable1 so 
           ON so.Name = mt.Name LEFT JOIN
           SupportTable2 st
           ON  st.Name = mt.Name
     ) s
WHERE Row BETWEEN 1 and 100000;

Notes:

• Window functions cannot be used in the WHERE clause.
• Column aliases cannot be used in the WHERE clause either; that is why a CTE or subquery is needed.
• Don't put single quotes around integer constants.

Alternatively, you can just use TOP:

      SELECT TOP (100000) mt.Name, mt.IDNO, so.IDType, Row = ROW_NUMBER() OVER (ORDER BY mt.Name)
      FROM MainTable mt LEFT JOIN
           SupportTable1 so 
           ON so.Name = mt.Name LEFT JOIN
           SupportTable2 st
           ON  st.Name = mt.Name
      ORDER BY Row;