Take the 2-minute tour ×
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.

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
add comment

2 Answers

up vote 2 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
add comment

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 
share|improve this answer
add comment

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.