Skip to content
Advertisement

SQL View Select Optional

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

  1. Each group can only have 2 code and always have a default code vs not a default code.
  2. IF default code is not null, then always show the row. Regardless if Non default code has value or not
  3. If default code is null, then show the none default code value only if it has value.
  4. 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

Demo on DB Fiddle:

Code | Value | GrpCode | GrpDft | rn
:--- | ----: | :------ | :----- | :-
A    |     1 | A1      | N      | 1 
D    |     2 | A2      | Y      | 1 
E    |  null | A3      | Y      | 1 
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement