Select rows with the same field values

Question

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 )

Check out this question: http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table — Brainless Box, Aug 15 '13 at 20:20

score 3 · Accepted Answer · answered Aug 15 '13 at 20:31

3

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

answered Aug 15 '13 at 20:31

Hart CO

34,064
6
48
63

Sure thing, as ultimately the inner join shown above will limit all results to multiples. – Hart CO Aug 16 '13 at 17:09

Ankit Bansal · Answer 2 · 2013-08-15T20:49:21.673

2

Shouldn't this work:

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

edited Aug 15 '13 at 20:49

answered Aug 15 '13 at 20:25

Ankit Bansal

4,962
1
23
40

Probably want `> 1` not `= 2` – Hogan Aug 15 '13 at 20:47
For some reason it doesn't like the ',' I get Incorrect syntax near ','. – user2531854 Aug 16 '13 at 11:19
I didn't realize you are using sql server. This is oracle syntax. The answer mentioned by "Goat CO" should work for sql server. – Ankit Bansal Aug 16 '13 at 13:17

i-one · Answer 3 · 2013-08-16T14:41:57.317

1

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' */

edited Aug 16 '13 at 14:41

answered Aug 15 '13 at 20:21

i-one

5,050
1
28
40

I was trying this method but I don't get the correct results. I have multiple JOINs in the query. `WITH MoreThanTwo AS ( SELECT t.Number, t.Date, n.RecordT, n.ReadLoc, cnt = COUNT(1) OVER (partition by RecordT, ReadLoc) FROM Table1 t join Table2 n ON t.Number = n.Number WHERE n.RecordT = 'N' AND n.ReadLoc = 'AWAY' ) SELECT * FROM MoreThanTwo WHERE cnt > 1` – user2531854 Aug 16 '13 at 14:26

Select rows with the same field values

3 Answers3