Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
1  
Which ID should go with each distinct Dept? And do you need the IDs to begin with? – Andriy M Mar 18 '13 at 13:10
1  
Your query is missing an ORDER BY clause that should match its TOP clause: ...(SELECT TOP (@RowTo) ... ORDER BY <whatever order you need>). – A-K Mar 18 '13 at 13:50
up vote 5 down vote accepted

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 ...
;
share|improve this answer

Use DENSE_RANK() instead

DENSE_RANK() associates the same rank value with peer rows that aren't strictly ordered given the window function's ORDER BY clause. Unlike RANK(), however, DENSE_RANK() produces no gaps, which is useful for DISTINCT semantics.

SELECT Dept
      ,ID      
  FROM (SELECT TOP (@RowTo) DENSE_RANK() 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 

How does it work?

This is best explained by example:

SELECT
  v, 
  ROW_NUMBER() OVER(ORDER BY v),
  RANK()       OVER(ORDER BY v),
  DENSE_RANK() OVER(ORDER BY v)
FROM (
  VALUES('a'),('a'),('a'),('b'),
        ('c'),('c'),('d'),('e')
) t(v);

The above yields:

| V | ROW_NUMBER | RANK | DENSE_RANK |
|---|------------|------|------------|
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |

I've recently written a blog post that explains this more thoroughly

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.