CODEWITHSUNDEEP
sqlsql-server
user38858
user38858

Reputation: 316

Select only the rows where column values appear more than once

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?

Upvotes: 0

Views: 944

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

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;

Upvotes: 1

VJ Hil
VJ Hil

Reputation: 904

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);

Upvotes: 2

Related Questions