SQL - help on "top 1 by group"

Question

Relevant tables:
DepartmentPhone: DepartmentPhoneID int, DepartmentID int, PhoneID int
Phone: PhoneID int, PhoneType int

There are 6 phones with PhoneType=4 that belong to DepartmentID=2. So this produces 6 records:

select *
from DepartmentPhone
  join Phone on Phone.PhoneID = DepartmentPhone.PhoneID and Phone.PhoneType = 4
where DepartmentPhone.DepartmentID = 2

Note that DepartmentID=2 is for illustration purposes and that my query will bring all departments.
What I want to achieve is select the first Phone (type=4) for each Department - only 1 row per department. I thought the following query would do the trick but it keeps retrieving all 6 records. What am i missing?

select x.*
from DepartmentPhone x
where 
  x.DepartmentID = 2
  and x.PhoneID = (select max(y.PhoneID)
                   from departmentphone y 
                     join Phone on y.PhoneID = Phone.PhoneID and Phone.PhoneType = 4
                   where x.DepartmentPhoneID = y.DepartmentPhoneID)

Thanks for your help!!!

Answer 1

I don't know your schema as well as you do, but would guess you need to correlate your groupings by DepartmentID, not DepartmentPhoneID.

select x.*
from DepartmentPhone x
where 
  x.DepartmentID = 2
  and x.PhoneID = (select max(y.PhoneID)
                   from departmentphone y 
                     join Phone on y.PhoneID = Phone.PhoneID and Phone.PhoneType = 4
                   where x.DepartmentID = y.DepartmentID);

Here's a couple of alternative queries that should get the same result without using a correlated subquery. The first uses a derived table:

select *
from DepartmentPhone x
join (select d.DepartmentID, max(d.PhoneID) as maxPhoneID
      from DpartmentPhone d join Phone p using (PhoneID)
      where p.PhoneType = 4
      group by d.DepartmentID) y
  using (DepartmentID);

The second alternative uses no subquery at all, but a self-join:

select d1.*
from DepartmentPhone d1
join Phone p1 on d1.PhoneID = p1.PhoneID and p1.PhoneType = 4
left outer join (DepartmentPhone d2 join Phone p2 
    on d2.PhoneID = p2.PhoneID and p2.PhoneType = 4)
  on d1.DepartmentID = d2.DepartmentID and d1.PhoneID < d2.PhoneID
where d2.DepartmentID is NULL;

Answer 2

I wish there were a clean syntax for this. The best is to use ROW_NUMBER:

;WITH DepartmentPhone_CTE AS
(
    SELECT p.*, 
        ROW_NUMBER() OVER
            (PARTITION BY dp.DepartmentID ORDER BY dp.PhoneID) AS RowNum
    FROM DepartmentPhone dp
    INNER JOIN Phone p
        ON p.PhoneID = dp.PhoneID
    WHERE p.PhoneType = 4
)
SELECT dp.*
FROM DepartmentPhone_CTE
WHERE RowNum = 1