CODEWITHSUNDEEP
sqlsql-server-2008
Crow
Crow

Reputation: 43

SQL Server Selecting and displaying all duplicate data based on 3 criteria

Using SQL Server, I would like to select all rows that have code x on the same date.
For example: The records must have the same ID, on the same date, and the same code (code x). I would then like to select only these rows (not just the 1 row with the ID that has a duplicate, but both rows that are duplicates).

To be displayed:

ID 3   10/1/16  Code X
ID 3   10/1/16  Code X

I have tried:

SELECT ID,Date,CODE
FROM (SELECT ID,Date,CODE
      FROM my table
      WHERE code = 'x' and date between '10-01-2016' and '10-31-2016'
      GROUP BY ID,date,code
      HAVING
      COUNT(*) > 1
     ) as T1
JOIN
    my table as T2 on
    T1.ID = T2.ID and 
    T1.Date = T2.Date and
    T1.code = T2.code
where code ='x' and date between '10-01-2016' and '10-31-2016'
group by ID,date,code

Upvotes: 1

Views: 41

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

select      *

from       (SELECT      count       (*) over (partition by  ID,date)                    as group_records
                       ,dense_rank  ()  over (order by      ID,date)                    as group_id
                       ,row_number  ()  over (partition by  ID,date order by getdate()) as row_within_group
                       ,t.*

            FROM        mytable t

            WHERE       code = 'x' 
                    and date between '10-01-2016' and '10-31-2016' 
            ) t

where       group_records > 1

order by    group_id
           ,row_within_group
;

Upvotes: 1

Mohsen Mirhoseini
Mohsen Mirhoseini

Reputation: 8882

If I got your question correctly, You can use this query:

select * from my_table where `ID` in
        (select `ID` from my_table group by `ID`,`Date`,`CODE` having count(*)>1)
    AND `Date` in
        (select `Date` from my_table group by `ID`,`Date`,`CODE` having count(*)>1)
    AND `CODE` in
        (select `CODE` from my_table group by `ID`,`Date`,`CODE` having count(*)>1)

I hope this answer helps :)

Upvotes: 0

Related Questions