Reputation: 2104
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
Reputation: 9724
Query:
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
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