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!!!