Consider the following table:
Column_A | Column_B | Column_C |
---|---|---|
1 | UserA | NULL |
2 | UserB | NULL |
3 | UserC | 1 |
4 | UserA | 1 |
5 | UserB | NULL |
6 | UserB | 2 |
7 | UserC | 2 |
I’d like to return all rows (Column_A, Column_B, Column_C) such that either:
- Column_C is NULL, or
- for every unique value in Column_C, return the first row with Column_B == UserA. If no such row exists, return the first row sorted by Column_B.time_created.
Column_A | Column_B (FK) | Column_C |
---|---|---|
1 | UserA | NULL |
2 | UserB | NULL |
4 | UserA | 1 |
5 | UserB | NULL |
6 | UserB | 2 |
Not sure how to do this in a single query.
select Column_A, Column_B, Column_C, min(case when Column_B = UserA then 0 else 1 end) as custom_order from Table where Column_B in (UserA, UserB, UserC) group by Column_C, Column_A order by Column_C nulls first, custom_order;
The min clause doesn’t de-dupe as expected.
Advertisement
Answer
Assuming you have analytic functions available…
I break this apart let’s get all the null values 1st.. Then union in the non nulls based on the order you want using an analytic (row_number), an embedded case expression, and two order bys.
SELECT Column_A, Column_B, Column_C, 0 as RN FROM TABLE WHERE COLUMN_C is null UNION ALL SELECT Column_A, Column_B, Column_C, RN FROM ( SELECT A.*, ROW_NUMBER() over (partition by A.column_C Order by case A.column_B when 'UserA' then 0 else 1 end, U.Time_Created) rn FROM Table A INNER JOIN user U on U.Column_B = A.Column_B WHERE A.Column_C is not null) B WHERE RN = 1
the whole user join is based on a comment not the question. it assumes table joins to user on column_B
We use a case statement to handle ordering the ‘UserA’s first and then time created. so if you have two “userA”‘s the first created will be first as well.