SQL Server ROW_NUMBER behavior

I have the following query:

SELECT * FROM
(
    SELECT 
       a.TeacherID, a.UniversityID, 
       ROW_NUMBER() OVER (ORDER BY a.TeacherID) AS RowNum 
    FROM 
       SelectAll a  
    LEFT JOIN 
       mp_Ratings r ON a.TeacherID = r.TeacherID 
    GROUP BY 
       a.TeacherID, a.UniversityID
) AS TeacherInfo 
WHERE RowNum BETWEEN 10 AND 50

and it works fine. Now, If I want to do something like:

SELECT * FROM
(
    SELECT 
       a.TeacherID, a.UniversityID, 
       ROW_NUMBER() OVER (ORDER BY a.TeacherID) AS RowNum 
    FROM 
       SelectAll a  
    LEFT JOIN 
       mp_Ratings r ON a.TeacherID = r.TeacherID 
    GROUP BY 
       a.TeacherID, a.UniversityID
) AS TeacherInfo 
WHERE RowNum BETWEEN 10 AND 50 AND UniversityID = 2

I get nothing because the UniversityID = 2 begins around row number 2991. I would like to be able to reset the Row_Number with every different UniversityID.

I tried the following:

SELECT *, ROW_NUMBER() OVER (ORDER BY TeacherID) AS RowNum  
FROM
(
    SELECT 
        a.TeacherID, a.UniversityID 
    FROM 
        SelectAll a  
    LEFT JOIN 
        mp_Ratings r ON a.TeacherID = r.TeacherID 
    GROUP BY 
        a.TeacherID, a.UniversityID
) AS TeacherInfo 
WHERE UniversityID = 2

which brings new set of row_numbers for every search, but if I try to use RowNum alias as such:

SELECT *, ROW_NUMBER() OVER (ORDER BY TeacherID) AS RowNum  
FROM
(
    SELECT 
        a.TeacherID, a.UniversityID 
    FROM 
        SelectAll a  
    LEFT JOIN 
        mp_Ratings r ON a.TeacherID = r.TeacherID 
    GROUP BY 
        a.TeacherID, a.UniversityID
) AS TeacherInfo 
WHERE UniversityID = 2 
  AND RowNum BETWEEN 10 AND 20

I get

Msg 207, Level 16, State 1, Line 4 Invalid column name 'RowNum'.

What is wrong with my select? Why can't I access the RowNum alias?

回答1:

You just need to move the WHERE clause to the inner query.

SELECT TeacherID, UniversityID, RowNum FROM
(
    SELECT a.TeacherID, a.UniversityID, ROW_NUMBER() OVER 
     (ORDER BY a.TeacherID) AS RowNum FROM SelectAll a
     LEFT JOIN mp_Ratings r 
     ON a.TeacherID = r.TeacherID 
     WHERE UniversityID = 2
     GROUP BY a.TeacherID, a.UniversityID
) as TeacherInfo WHERE RowNum BETWEEN 10 AND 50;

You can't access the RowNum alias in the outer version of the query because the alias doesn't exist yet. SELECT is parsed second-last, before ORDER BY. WHERE is processed before SELECT.

回答2:

Use partition on row_number:

row_number() over(partition by UniversityID, order by teacherid)