I'm using CTEs along with a JOIN to pull personnel information. This works fine.

Now, I want to try to pull more information from another table, "Phones". Phones contains multiple phone numbers, of multiple types, for personnel. Each person's phone are also ranked in order of importance and they can have multiple phones of the same type (cell, home, work, etc).

Phones
PhoneID
PersonnelID
PhoneType
PhoneNumber
SequenceNo

Now, what I'm trying to do is continue pulling all Personnel, but also the highest phone number of a particular type (optimally in the same SP).

What I have so far:

Code SQL:
CREATE PROCEDURE dbo.Personnel_GetContactHelpSummariesByStartingNodeID
(
  @StartingNodeID INT,
  @PhoneType CHAR(3)
)
AS
 
WITH Nodes (NodeID, ParentNodeID, SequencePath)
AS
(
SELECT NodeID, ParentNodeID, SequencePath 
	FROM Personnel_CommandHierarchy 
	WHERE NodeID = @StartingNodeID
 
UNION ALL
 
SELECT CH.NodeID, CH.ParentNodeID, CH.SequencePath FROM Personnel_CommandHierarchy CH JOIN Nodes R
 ON R.NodeID = CH.ParentNodeID
)
 
SELECT 
	Personnel_Personnel.PersonnelID,
	Personnel_Personnel.FirstName, 
	Personnel_Personnel.MiddleInitial, 
	Personnel_Personnel.LastName,
	Personnel_Personnel.SuffixCode,
	Personnel_Personnel.CommandHierarchyNodeID,
	Personnel_Personnel.ServiceMember,
 
	Personnel_Phones.PhoneNumber,
 
	Personnel_CivilianPersonnel.CivilianClassificationCode,
	Personnel_CivilianPersonnel.CivilianRankCode,
 
	Personnel_ServicePersonnel.ServiceRankCode
FROM 
	Nodes 
INNER JOIN 
	Personnel_Personnel 
ON 
	Nodes.NodeID = Personnel_Personnel.CommandHierarchyNodeID
LEFT OUTER JOIN
  Personnel_Phones
ON
  Personnel_Personnel.PersonnelID = Personnel_Phones.PersonnelID
INNER JOIN
  Personnel_CivilianPersonnel
ON
  Personnel_CivilianPersonnel.PersonnelID = Personnel_Personnel.PersonnelID
INNER JOIN
	Personnel_ServicePersonnel
ON
  Personnel_ServicePersonnel.PersonnelID = Personnel_Personnel.PersonnelID
WHERE 
	Personnel_Personnel.CommandHierarchyNodeID = Nodes.NodeID AND
	Personnel_Personnel.Archived = 0
ORDER BY 
	Nodes.SequencePath, Personnel_Personnel.LastName, Personnel_Personnel.FirstName
 
 
RETURN

Since I'm already using the WHERE clause to join my Personnel table to the table created by the CTE, I'm not sure where I can indicate that I only want the top-ranked phone number of a particular type.