CODEWITHSUNDEEP
sql
spyder
spyder

Reputation: 35

SQL DISTINCT values across rows

I have duplicate agreementnumber and duplicate telephone in columns and I want to get unique agreementnumber and it's corresponding unique telephone in columns.

I have written query in SQL which gives me unique agreementnumber but telephone in rows are duplicate but I want unique phone numbers.

code:

select agreementnumber,

  max(case when rn = 1 then telephone end) telephone1,

  max(case when rn = 2 then telephone end) telephone2,

  max(case when rn = 3 then telephone end) telephone3,

  max(case when rn = 4 then telephone end) telephone4,

  max(case when rn = 5 then telephone end) telephone5

from
(
  select agreementnumber, telephone,

    row_number() over(partition by agreementnumber order by telephone) rn
  from alternate_mobile 

) src
group by agreementnumber;

I want following output. Unique values in col1 and col2,col3,col4,col4.

col1 col2 col3 col4`` AGMTNO phone1 phone2 phone3

Upvotes: 0

Views: 91

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270361

As a note, you can reduce the number of subqueries by using rank() instead of row_number():

select agreementnumber,
       max(case when rn = 1 then telephone end) as telephone1,
       max(case when rn = 2 then telephone end) as telephone2,   
       max(case when rn = 3 then telephone end) as telephone3,
       max(case when rn = 4 then telephone end) as telephone4,
       max(case when rn = 5 then telephone end) as telephone5
from (select am.*,
             rank() over (partition by am.agreementnumber order by am.telephone) as rn
      from alternate_mobile am
     ) am
group by agreementnumber;

Upvotes: 1

James
James

Reputation: 3015

Try with this little change in your query:

select agreementnumber,
  max(case when rn = 1 then telephone end) telephone1,
  max(case when rn = 2 then telephone end) telephone2,   
  max(case when rn = 3 then telephone end) telephone3,
  max(case when rn = 4 then telephone end) telephone4,
  max(case when rn = 5 then telephone end) telephone5

from
(
  select x.*,
    row_number() over(partition by x.agreementnumber order by x.telephone) rn
  from (
    select distinct agreementnumber, telephone
    from alternate_mobile 
  ) x
) src
group by agreementnumber;

If you were getting duplicate telephones is because you have duplicated agreementnumber/telephone in your alternate_mobile table.

Edited:

I change the query to keep just numbers in the telephone, removing all the rest of characters:

select agreementnumber,
  max(case when rn = 1 then telephone end) telephone1,
  max(case when rn = 2 then telephone end) telephone2,   
  max(case when rn = 3 then telephone end) telephone3,
  max(case when rn = 4 then telephone end) telephone4,
  max(case when rn = 5 then telephone end) telephone5

from
(
  select x.*,
    row_number() over(partition by x.agreementnumber order by x.telephone) rn
  from (
    select distinct agreementnumber, regexp_replace(telephone,'[^0-9]', '') as telephone
    from alternate_mobile 
  ) x
) src
group by agreementnumber;

Upvotes: 1

Related Questions