Search duplicated values and show their context

Question

I'm not sure if the solution for this question is incredible simple or not possible in pure SQL.

I have a simple table with 2 columns

Number    Text
1         a
1         b
2         m
3         x
3         y
3         z

Now the task is: Search all repeated numbers and show the "Text" which uses these duplicated numbers.

We see: 1 is used twice (with a and b), 3 is used with x and y and z. But no line is completely duplicated.

Edit: So I expect something like this.

   Dup_Num    Text
    1         a
    1         b
    3         x
    3         y
    3         z

The search for the duplicate is easy, but I don't have an idea how to connect is with "Text", because when I add "Text" to my SELECT I have to use it for GROUP and this give no duplicates ..

Thanks for help on a lousy day ..

With the sample data above, what are your expected results? I'm not sure I understand the question. — Neville Kuyt, Jul 24 '20 at 08:52
(Sorry, I'm to simple to understand the formatting here in the comments): I expect a grid like this: dup_nums Text 1 a 1 b 3 x 3 y 3 z Or maybe in another form like dup_nums all_texts 1 a, b 3 x,y,z — Pierre de la Verre, Jul 24 '20 at 09:12
@PierredelaVerre . . . You should edit the *question* not add a comment. What the results look like is a critical part of the question. — Gordon Linoff, Jul 24 '20 at 11:31

score 1 · Answer 1 · answered Jul 24 '20 at 09:57

1

The canonical way to find duplicates in SQL is the self join.

In your example:

select s1.*
from stuff s1
inner join stuff s2
  on s1.number = s2.number
  and s1.text <> s2.text

answered Jul 24 '20 at 09:57

Neville Kuyt

29,247
1
37
52

Thanks Neville; I modified it a little bit to "SELECT DISTINCT S1.*..." and get exactly what I wanted. – Pierre de la Verre Jul 24 '20 at 11:39
1

@PierredelaVerre . . . `SELECT DISTINCT` significantly slows down the query. The `EXISTS` solution should have better performance. – Gordon Linoff Jul 24 '20 at 12:51
Gordon's solution is probably the faster one. It usually is. – Neville Kuyt Jul 24 '20 at 14:03
Thanks for commenting. I changed now the "correct solution" – Pierre de la Verre Jul 29 '20 at 14:12

Gordon Linoff · Accepted Answer · 2020-07-24T12:51:47.500

1

If I understand correctly, you can use exists:

select t.*
from t
where exists (select 1 from t t2 where t2.number = t.number and t2.text <> t.text)
order by t.number;

For performance, you want an index on (number, text).

edited Jul 24 '20 at 12:51

answered Jul 24 '20 at 11:32

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon, that's a fine solution too. – Pierre de la Verre Jul 24 '20 at 12:45

score 0 · Answer 3 · answered Jul 24 '20 at 09:21

In your case, you might want to use LISTAGG to group those values and its relation to the other column

SQL> with result 
as (
select '1' as c1 , 'a' as c2 from dual union all
select '1' as c1 , 'b' as c2 from dual union all
select '2' as c1 , 'm' as c2 from dual union all
select '3' as c1 , 'x' as c2 from dual union all
select '3' as c1 , 'y' as c2 from dual union all
select '3' as c1 , 'z' as c2 from dual )
select c1, listagg(c2,',') within group(order by c1) as c3 from result
group by c1;

C C3
- --------------------
1 a,b
2 m
3 x,y,z

SQL>

Thanks Roberto; this solution would be a part of the second option. The poster below showed me the best solution. — Pierre de la Verre, Jul 24 '20 at 11:36

score 0 · Answer 4 · answered Jul 24 '20 at 12:44

0

This might also help you.

 select * from t where Number in 
 (select Number from t group by Number having count(*) > 1)
 order by Text

Fiddle

answered Jul 24 '20 at 12:44

Dilhan Nakandala

301
5
24

Search duplicated values and show their context

4 Answers4