same 'num' but different value

user_16957404 0
Viewed 53

I am new to Oracle's SQL and need help. How can I select only those rows that have the same 'code' for a different 'name'?

For example I have Table 1 and I want to get Table 2.

Table 1: customer

id name num_id
1 Jenny 456
2 Jenny 456
3 Ben 456
4 Ursula 879
5 Mike 145
6 Mike 145
7 Nick 954

Table 2: customer

id name num_id
1 Jenny 456
3 Ben 456

This is my query:

select distinct t1.name, t1.num_id
  from customer t1
  join (select distinct name, num_id, count(*)
          from customer
         group by name, num_id
        having count(*) > 1) t2
    on t1.num_id = t2.num_id
   and t1.name <> t2.name
 order by 2;

But it does not work. Can you help me?

oracle sql
2 Answers

No need for the name in the subquery. You need to find num_id which has multiple names, therefore group by num_id and count(distinct name).

select distinct t1.name, t1.num_id
from customer t1
    join (
        select num_id, count (distinct name)
        from customer
        group by num_id
        having count (distinct name) > 1 
) t2 on t1.num_id = t2.num_id
order by 2;
user_81032630

You can also use below sql to get the job done.

  • First, in the inline view t, I use count analytic function to make oracle count distinct values per num_id.
  • Then, I filter out the result from the inline t with cnt > 1 condition
  • Then, I use group by clause to aggregate the result from the inline t per name and num_id columns. Once that, you just need to use min aggregate function to select the minimum id.
select min(id) id, name, num_id
from (
    select id, name, num_id, count(distinct name) over (partition by num_id) cnt
    from Your_Customer_Table
) t
where cnt > 1
group by name, num_id
order by 1

demo

user_136191160
Related