girish girish -4 years ago 74
SQL Question

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download