SQL Query, I need 1 line per person with lowest Id of two columns

Question

SELECT 
    Mem.MemberID, Mem.LastName + ', ' + Mem.FirstName AS Name, 
    MD.DiagnosisID, 
    Diag.DiagnosisDescription, 
    DC.DiagnosisCategoryID, DC.CategoryDescription, DC.CategoryScore
FROM 
    Member AS Mem
LEFT OUTER JOIN 
    MemberDiagnosis AS MD ON Mem.MemberID = MD.MemberID
LEFT OUTER JOIN 
    Diagnosis AS Diag ON MD.DiagnosisID = Diag.DiagnosisID
LEFT OUTER JOIN 
    DiagnosisCategoryMap AS Map ON Map.DiagnosisID = Diag.DiagnosisID
LEFT OUTER JOIN 
    DiagnosisCategory AS DC ON DC.DiagnosisCategoryID = Map.DiagnosisCategoryID
ORDER BY 
    Name ASC

I have this query, which returns the following results:

ID  | Name          | D.ID | D.Desc         | C.ID | C. Desc     | C.Score
----+---------------+------+----------------+------+-------------+----------
2   | Smith, Jack   | NULL | NULL           | NULL | NULL        | NULL
1   | Smith, John   | 2    | Test Diagnosis | 2    | Category B  | 20
1   | Smith, John   | 4    | Test Diagnosis | 3    | Category C  | 30
3   | Smyth, Will   | 3    | Test Diagnosis | 3    | Category C  | 30
3   | Smyth, Will   | 4    | Test Diagnosis | 3    | Category C  | 30

With in that query and result set, how can I go about returning 1 line per person with;

1. The lowest Diagnosis ID and it's desc.
2. The lowest Category ID with desc and score.
3. In stead of null category, return Category ID 1 and its desc and score.

Answer 1

Try the following query:

SELECT T1.*,Diag2.DiagnosisDescription, DC2.CategoryDescription, DC2.CategoryScore 
FROM (SELECT DISTINCT 

    Mem.MemberID, Mem.LastName + ', ' + Mem.FirstName AS Name, 
    MIN(MD.DiagnosisID) OVER(PARTITION BY  Mem.MemberID) AS DiagnosisID, 

    MIN(ISNULL(DC.DiagnosisCategoryID,1)) OVER(PARTITION BY  Mem.MemberID) AS DiagnosisCategoryID
FROM 
    Member AS Mem
LEFT OUTER JOIN 
    MemberDiagnosis AS MD ON Mem.MemberID = MD.MemberID
LEFT OUTER JOIN 
    Diagnosis AS Diag ON MD.DiagnosisID = Diag.DiagnosisID
LEFT OUTER JOIN 
    DiagnosisCategoryMap AS Map ON Map.DiagnosisID = Diag.DiagnosisID
LEFT OUTER JOIN 
    DiagnosisCategory AS DC ON ISNULL(DC.DiagnosisCategoryID,1) = ISNULL(Map.DiagnosisCategoryID,1) ) AS T1 

LEFT JOIN   Diagnosis AS Diag2 ON T1.DiagnosisID = Diag2.DiagnosisID
LEFT JOIN   DiagnosisCategory AS DC2 ON T1.DiagnosisCategoryID = DC2.DiagnosisCategoryID
ORDER BY T1.NAME