Filtering unique values

Question

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:

1. Column_C is NULL, or
2. 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.

Answer 1

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.

Answer 2

Thanks xQbert for your help. Updated the answer to fix a few things:

1. No comma between partition and order
2. Filter by user_ids in each subquery - there may be users in the table we don't care about.

'''

SELECT Column_A, Column_B, Column_C, 0 as RN
FROM TABLE
WHERE COLUMN_C is null and Column_B in (UserA, UserB, UserC)

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 and ColumnB in (userA, userB, UserC)) B
WHERE RN = 1

'''