Issue with ROW_NUMBER and DISTINCT

Question

I have following query

SELECT Dept
      ,ID      
  FROM (SELECT TOP (@RowTo) ROW_NUMBER() OVER (ORDER BY Dept) AS ROWID,      
               Dept    
              ,ID
          FROM Department 
         WHERE Dept LIKE @Team + '%') as Calls        
 WHERE RowID >= @RowFrom AND RowID <= @RowTo 

I need to get a DISTINCT list of the Departments, but need the paging that this query returns. I have tried to rewrite this many a times, but I'm failing miserably. Anyone got any pointers?

Table data

ID    Department  Location
1     HR          London
2     HR          Berlin
9     HR          Paris
11    HR          Amsterdam
12    IT          Berlin
13    IT          London
15    HR          New York
18    IT          Paris
19    IT          Barcelona
20    HR          Barcelona
21    Finance     Paris
22    Finance     London
23    Finance     New York

Using the following SQL:

SELECT Dept
      ,ID      
  FROM (SELECT TOP (@RowTo) ROW_NUMBER() OVER (ORDER BY Dept) AS ROWID,      
               Dept    
              ,ID
          FROM Department 
         WHERE Dept LIKE @Team + '%'
         ORDER
            BY Dept) as Calls        
 WHERE RowID >= @RowFrom AND RowID <= @RowTo 

Returns:

Dept    ID
HR      1
HR      2
HR      9
HR      11
IT      12
IT      13
HR      15
IT      18
IT      19
HR      20

Now, I know that I need to drop out the ID column in order to get a DISTINCT list of values for the Department column. By doing so, I only get:

Dept
HR
IT

None of the Finance Departments are dropping into the result set because the sub query is returning the TOP 10 records which it's not part of. This is where I'm no getting stuck.

Answer 1

DISTINCT is evaluated last or almost last of all the clauses. Therefore, it can't be used on the same level as ROW_NUMBER to get the results you want. Either use a derived table with DISTINCT:

SELECT
  Dept
FROM (
  SELECT
    Dept,
    ROW_NUMBER() OVER (ORDER BY Dept) AS RowID
  FROM (
    SELECT DISTINCT
      Dept
    FROM Department
    WHERE Dept ... 
  ) AS d
) AS Calls
WHERE RowID ...
;

or replace DISTINCT with GROUP BY:

SELECT
  Dept
FROM (
  SELECT
    Dept,
    ROW_NUMBER() OVER (ORDER BY Dept) AS RowID
  FROM Department
  WHERE Dept ...
  GROUP BY Dept
) AS Calls
WHERE RowID ...
;

Answer 2

Your query is missing an ORDER BY clause that should match its TOP clause:

SELECT Dept
      ,ID      
  FROM (SELECT TOP (@RowTo) ROW_NUMBER() OVER (ORDER BY Dept) AS ROWID,      
               Dept    
              ,ID
          FROM Department 
         WHERE Dept LIKE @Team + '%'
ORDER BY <whatever order you need>
) as Calls        
 WHERE RowID >= @RowFrom AND RowID <= @RowTo