Select only the rows where column values appear more than once

Question

I have a select statement similar to the following:

select *
from A
  inner join B on A.id_x = B.id_x
  inner join C on B.id_y = C.id_y
  inner join D on C.id_z = D.id_z
where
  A.date > '2014-01-01'
  and A.id_y = 154
  and D.id_t = 2

What I want is to do something like this and count(A.id_x) > 1, which returns only the parts of the original select which repeat on A.id_x.

Is this possible?

EDIT:

I just tried to solve it using temp tables, with the code I got from T-SQL Insert into table without having to specify every column

Select * Into 
    #tmpBigTable
    From [YourBigTable]

But I got an error message because my tables have the same column names, A.id_x and B.id_x, for example.

"Column names in each table must be unique."

Is there some way to force the issue, or declare arbitrary naming extensions?

score 2 · Accepted Answer · answered Jun 05 '14 at 00:25

2

select *
from A
  inner join B on A.id_x = B.id_x
  inner join C on B.id_y = C.id_y
  inner join D on C.id_z = D.id_z
where
  A.date > '2014-01-01'
  and A.id_y = 154
  and D.id_t = 2
  AND  A.id_x IN
  (
  SELECT A.id_x FROM A
  GROUP BY A.id_x
  HAVING count(A.id_x)>1);

answered Jun 05 '14 at 00:25

VJ Hil

904
6
15

Thanks. That seemed to work. I tried it and kept getting no results (but no error) and thinking something was wrong, until I tested it with `>0`, and realized there were no cases for that particular set of clauses. – user38858 Jun 05 '14 at 00:32

score 1 · Answer 2 · answered Jun 05 '14 at 00:40

You can do this with window functions:

select *
from (select *, count(*) over (partition by A.id_x) as cnt
      from A inner join
           B
           on A.id_x = B.id_x inner join
           C
           on B.id_y = C.id_y inner join
           D
           on C.id_z = D.id_z
      where A.date > '2014-01-01' and A.id_y = 154 and D.id_t = 2
     ) abcd
where cnt > 1;

Select only the rows where column values appear more than once

2 Answers2