user2920788 user2920788 - 1 month ago 15
SQL Question

Join certain rows to the results

I'm trying to make a query that will call get all that matches

col3 = 1
and get the rest of the group
col4 = 123
while having col2 be the distinct value. My table looks like

ID col1 col2 col3 col4
---------------------------------
1 A1 A NULL 123
2 B1 B NULL 123
3 C1 C NULL 123
4 D1 D NULL 123
5 C2 C 1 123
6 D2 D 1 123


and I am trying to make a query that would return IDs 1,2,5 and 6. Have tried unions and joins off variations of
select * from tbl where col4 = 123 and col3 =1
and they all either exclude 3,4,5,6 or include them all.

Answer
select      *


from       (select      *

                       ,row_number () over
                        (
                            partition by    col2
                            order by        case when col3 = 1 then 1 else 2 end
                        ) as rn

            from        t

            where       col4 = 123
            ) t

where       col3 = 1
        or  t.rn = 1
;