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;

Answer 2

Starting with Sql Server 2012, OFFSET and FETCH NEXT were added to the ORDER BY clause.

SELECT mt.Name, mt.IDNO, so.IDType
FROM MainTable mt WITH(NOLOCK)
LEFT JOIN SupportTable1 so WITH(NOLOCK) ON so.Name = mt.Name
LEFT JOIN SupportTable2 st WITH(NOLOCK) ON st.Name = mt.Name
ORDER BY mt.Name OFFSET 0 ROWS FETCH NEXT 100000 ROWS ONLY;

Then add 100000 to the OFFSET number with each new iteration.