I have a view that the current output something like this
Code Value GrpCode GrpDft -------------------------- A, 1, A1, N B, null, A1, Y C, 1, A2, N D, 2, A2, Y E, null, A3, Y F, null, A3, N
The rules are as follows
- Each group can only have 2 code and always have a default code vs not a default code.
- IF default code is not null, then always show the row. Regardless if Non default code has value or not
- If default code is null, then show the none default code value only if it has value.
- If both of default and non default code are null, then show default value.
So based on above I should have below row remaining.
A, 1, A1, N D, 2, A2, Y E, null, A3, Y
Unfortunately due to other factors, this has to be done as a view and not stored procedure or functions.
Advertisement
Answer
If I followed you correctly, you can implement that logic with window functions:
select * from ( select t.*, row_number() over( partition by grpCode order by case when grpDft = 'Y' and value is not null then 0 when grpDft = 'N' and value is not null then 1 else 2 end, grpDft desc ) rn from mytable t ) t where rn = 1
Code | Value | GrpCode | GrpDft | rn :--- | ----: | :------ | :----- | :- A | 1 | A1 | N | 1 D | 2 | A2 | Y | 1 E | null | A3 | Y | 1