Q:

How to remove swap columns in below data getting from SQL query?

I am getting data using below SQL query.
select * 
from 
    (select 
         c.cdr_id as cdr_id, 
         (select cdr_id 
          from coms_trnsfrmd.cdr_legacy 
          where cgs_cdr_id = k.assc_cdr_id) as associated_cdr_id 
     from 
         cgs_postgres.kl_cgs_associated_cdrs k 
     inner join 
         coms_trnsfrmd.cdr_legacy c on k.cdr_id = c.cgs_cdr_id )temp 
where 
    temp.associated_cdr_id is not null 
order by 
    temp.cdr_id

Data is look like below using above sql query.
CDR_ID          ASSOCIATED_CDR_ID
123                 456
456                 123
123                 178
178                 123
156                 169
198                 456
456                 198

Case 1: if records looks like swap
CDR_ID    ASSOCIATED_CDR_ID
 123             456
 456             123

I don't need populate both, I need it either first or second record.
Case 2: if there is no swap records like
CDR_ID    ASSOCIATED_CDR_ID
156            169 

I need it direct populate it into target.

A:

Try this:
with your_table
as (
    select *
    from (
        select c.cdr_id as cdr_id,
            (
                select cdr_id
                from coms_trnsfrmd.cdr_legacy
                where cgs_cdr_id = k.assc_cdr_id
                ) as associated_cdr_id
        from cgs_postgres.kl_cgs_associated_cdrs k
        inner join coms_trnsfrmd.cdr_legacy c on k.cdr_id = c.cgs_cdr_id
        ) temp
    where temp.associated_cdr_id is not null
    )
----- The above is your original query. Actual solution is below.---- 

select *
from your_table
where CDR_ID <= ASSOCIATED_CDR_ID

union all

select *
from your_table t
where CDR_ID > ASSOCIATED_CDR_ID
    and not exists (
        select 1
        from your_table t2
        where t.CDR_ID = t2.ASSOCIATED_CDR_ID
            and t2.CDR_ID = t.ASSOCIATED_CDR_ID
        )

not exists ensures that no two rows have swappable values.

