Aycan Yaşıt
Aycan Yaşıt

Reputation: 2104

Show only first row of each group excluding NULL

I have a table of patients. Patients may be single or married. I want to list all of the patients in alphabetical order, but I want to show only female patient for married couples (ignore the husband).
If multiple patients have same CaseNo, I consider they are couples.
Genders: 0 is female, 1 is male.

PatientID  CaseNo  Gender  Name
1          1       1       Mike
2          NULL    0       Pamela
3          2       0       Mary
4          2       1       John
5          NULL    1       Dave

Expected output is:

PatientID  CaseNo  Gender  Name
5          NULL    1       Dave
3          2       0       Mary
1          1       1       Mike
2          NULL    0       Pamela

John is excluded because his wife is on the list.

I tried one of the answers in this question, but it excludes the records which have CaseNo as NULL.

WITH cte 
     AS (SELECT PatientID, 
                CaseNo, 
                Gender, 
                Name, 
                Row_number() 
                  OVER ( 
                    partition BY CaseNo
                    ORDER BY Gender DESC) rn 
         FROM Patients) 
SELECT PatientID, 
       CaseNo, 
       Gender, 
       Name

FROM   cte 
WHERE  rn = 1 
ORDER BY Name

Is it possible to achieve this?

Upvotes: 1

Views: 211

Answers (2)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

  WITH cte 
     AS (SELECT PatientID, 
                CaseNo, 
                Gender, 
                Name, 
               CASE WHEN CaseNo is null
               THEN 1 
               ELSE  Row_number() 
                  OVER ( 
                    partition BY CaseNo
                    ORDER BY Gender ASC) END  rn 
         FROM Patients) 
SELECT PatientID, 
       CaseNo, 
       Gender, 
       Name

FROM   cte 
WHERE  rn = 1 
ORDER BY Name asc

Result:

| PATIENTID | CASENO | GENDER |   NAME |
|-----------|--------|--------|--------|
|         5 | (null) |      1 |   Dave |
|         3 |      2 |      0 |   Mary |
|         1 |      1 |      1 |   Mike |
|         2 | (null) |      0 | Pamela |

Upvotes: 1

peterm
peterm

Reputation: 92785

You can try it this way

SELECT PatientID, CaseNo, Gender, Name
  FROM 
(
  SELECT PatientID, CaseNo, Gender, Name, 
         ROW_NUMBER() OVER (PARTITION BY CaseNo ORDER BY Gender) rnum,
         COUNT(*) OVER (PARTITION BY CaseNo) rcnt
    FROM Patients
) q
 WHERE CaseNo IS NULL 
    OR rcnt = 1
    OR (rcnt > 1 AND rnum = 1)
 ORDER  BY Name

Output:

| PATIENTID | CASENO | GENDER |   NAME |
|-----------|--------|--------|--------|
|         5 | (null) |      1 |   Dave |
|         3 |      2 |      0 |   Mary |
|         1 |      1 |      1 |   Mike |
|         2 | (null) |      0 | Pamela |

Here is SQLFiddle demo

Upvotes: 2

Related Questions