Consider the following table:

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.

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.

CodePudding user response：

UNTESTED:

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.

CodePudding user response：

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

'''