patternMinor

Issue with ROW_NUMBER and DISTINCT

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

distinctwithissuerow_numberand

Problem

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.

Solution

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 ...
;

Code Snippets

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 ...
;

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

Context

StackExchange Database Administrators Q#36920, answer score: 5

Revisions (0)

No revisions yet.