remove subquery

Question

I have table with one column, and I need to get all duplicated lines. This is table:

|  name  |
|  ----- |
|    a   |
|    a   |
|    b   |
|    c   |
|    c   |
|    d   |

And this is expected result set:

|  name  |
|  ----- |
|    a   |
|    a   |
|    c   |
|    c   |

I already do this that way:

 SELECT t.name FROM my_table t inner join (
        SELECT name, count(1) AS count FROM my_table t2 GROUP BY t2.name HAVING count > 1
      ) AS t1 on t1.name = t.name

Is it possible to do it without subquery?

Note that the absence of a PRIMARY KEY may prove problematic further down the road. — Strawberry, Jan 25 '17 at 12:59
@krishn patel have no idea how to do it without 2 subqueries. — degr, Jan 25 '17 at 13:02

score 3 · Answer 1 · edited Jan 05 '22 at 03:58

3

If you have only one column, wouldn't this output do?

select name, count(*) as num_duplicates
from t
group by name
having count(*) > 1;

This produces one row per name with the number of duplicates. If you have only one column, that seems like a better output (to me) than the list of names repeated.

edited Jan 05 '22 at 03:58

Nimantha

6,405
6
28
69

answered Jan 25 '17 at 12:47

Gordon Linoff

1,242,037
58
646
786

if you do it. it will bring single time a c not a a c c – krishn Patel Jan 25 '17 at 12:53

score 1 · Answer 2 · answered Jan 25 '17 at 13:34

1

you need to first bring name of having count >1

select * from my_table where name in 
(select name from my_table having count(name) > 1 group by name )

answered Jan 25 '17 at 13:34

krishn Patel

2,579
1
19
30

Thanks, I see. But actually this is same as my example, but work much more slower – degr Jan 25 '17 at 13:37

remove subquery

2 Answers2