CODEWITHSUNDEEP
sqldatabaseoracle-database
Andy Li
Andy Li

Reputation: 21

Filter repeated columns in Oracle

I want to see the same pid(or id from the person_info table) only once with the sql below. Adding "group by pid" in the end would work in MySQL but not in Oracle because I would have to have a group by for each after select. Distinct doesn't work here either because it returns unique combinations and not just pid. This is driving me crazy... Thanks in advance!

select pi.id                 pid,
   n.id                  nid,
   n.match_mode,
   n.match_method,
   n.match_gene_type,
   n.notification_status,
   pi.person_name,
   pi.id_card_no,
   pi.race
from notification n, person_info pi
where (n.src_data_id = pi.id or n.match_data_id = pi.id)
and n.match_mode = '1'
and n.match_gene_type = '1'
and n.notification_status = '2'
and pi.id_card_no IS NOT NULL

Upvotes: 2

Views: 53

Answers (2)

dimo raichev
dimo raichev

Reputation: 580

If you dont care which row do you get you can get the MAX() value of each column.

select pi.id                 pid,
   MAX(n.id)                  nid,
   MAX(n.match_mode),
   MAX(n.match_method),
   MAX(n.match_gene_type),
   MAX(n.notification_status),
   MAX(pi.person_name),
   MAX(pi.id_card_no),
   MAX(pi.race)
from notification n, person_info pi
where (n.src_data_id = pi.id or n.match_data_id = pi.id)
and n.match_mode = '1'
and n.match_gene_type = '1'
and n.notification_status = '2'
and pi.id_card_no IS NOT NULL
group by pi.id

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use window function row_number()

with cte as
(
select pi.id                 pid,
   n.id                  nid,
   n.match_mode,
   n.match_method,
   n.match_gene_type,
   n.notification_status,
   pi.person_name,
   pi.id_card_no,
   pi.race,
   row_number() over(partition by pi.id order by pi.id) as rn
from notification n, person_info pi
where (n.src_data_id = pi.id or n.match_data_id = pi.id)
and n.match_mode = '1'
and n.match_gene_type = '1'
and n.notification_status = '2'
and pi.id_card_no IS NOT NULL
) select * from cte where rn=1

Upvotes: 2

Related Questions