CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user2531854
user2531854

Reputation: 866

Select rows with the same field values

How can I query only the records that show up twice in my table?

Currently my table looks something like this:

Number   Date                  RecordT     ReadLoc
123      08/13/13 1:00pm       N           Gone
123      08/13/13 2:00pm       P           Home
123      08/13/13 3:00pm       N           Away
123      08/13/13 4:00pm       N           Away

I need a query that will select the records that have the same 'Value' in the RecordT field and the same 'Value' in the ReadLoc field.

So my result for the above would show with the query:

Number   Date                  RecordT     ReadLoc
123      08/13/13 3:00pm       N           Away
123      08/13/13 4:00pm       N           Away

I was trying to do a subselect like this:

SELECT t.Number, t.Date, n.RecordT, n.ReadLoc
FROM Table1 t join Table2 n ON t.Number = n.Number
WHERE t.Number IN (SELECT t.Number FROM Table1 GROUP BY t.Number HAVING COUNT(t.Number) > 1 )
AND n.ReadLoc IN (SELECT n.ReadLoc FROM Table2 GROUP n.ReadLoc HAVING COUNT(n.ReadLoc) > 1 )

Upvotes: 1

Views: 3161

Answers (3)

i-one
i-one

Reputation: 5120

The following can be taken as a base:

;with cte as (
    select *, cnt = count(1) over (partition by RecordT, ReadLoc)
    from TableName
)
select *
from cte
where cnt > 1

If your TableName is actually a view of two joined tables, try:

;with TableName as (
    SELECT t.Number, t.Date, n.RecordT, n.ReadLoc
    FROM Table1 t
        join Table2 n ON t.Number = n.Number
),
cte as (
    select Number, Date, RecordT, ReadLoc,
        cnt = count(1) over (partition by RecordT, ReadLoc)
    from TableName
)
select Number, Date, RecordT, ReadLoc
from cte
where cnt > 1 /* and RecordT='N' and ReadLoc='AWAY' */

Upvotes: 1

Ankit Bansal
Ankit Bansal

Reputation: 5072

Shouldn't this work:

select * 
from table1 
where (RecordT, ReadLoc) in 
   (select RecordT, ReadLoc 
    from table1 
    group by RecordT, ReadLoc 
    having count(*) > 1)

Upvotes: 2

Hart CO
Hart CO

Reputation: 34774

SELECT a.*
FROM Table1 a
JOIN (SELECT RecordT, ReadLoc
      FROM Table1
      GROUP BY RecordT, ReadLoc
      HAVING COUNT(*) > 1
      )b
ON a.RecordT = b.RecordT
 AND a.ReadLoc = b.ReadLoc

SQL Fiddle

Upvotes: 3

Related Questions