SQL Return only duplicate rows

Question

I have a query that returns the following rows:

StateId, OrderId, OrderTime, PermitId

I need to return only the rows that are exact duplicates all across the board so each record must be exctly the same as the other record for it to be a duplicate. I would like to return both records. These reocrds are mixed in with a bunch of records that do not have duplicates...

Any idea?

Answer 1

First, identify the duplicates. Second, join back to extract these rows.

A non-aggregated (or non-window/ranking) self join forms a partial cross join and gives the square of duplicates for any set of keys. Including non-duplicates too. 1 x 1 = 1 after all.

SELECT
    t2.*
FROM
    (
    SELECT 
       StateId, OrderId, OrderTime, PermitId
    FROM
       myTable
    GROUP BY
       StateId, OrderId, OrderTime, PermitId
    HAVING
       COUNT(*) >= 2
    ) T1
    JOIN
    mytable T2 ON T1.StateId = T2.StateId AND T1.OrderId = T2.OrderId AND
                   T1.OrderTime = T2.OrderTime AND T1.PermitId = T2.PermitId

Answer 2

In general, if you're just trying to see what rows have duplicate for those values...

SELECT StateId, OrderId, OrderTime, PermitId, COUNT(*) FROM Foo
GROUP BY StateId, OrderId, OrderTime, PermitId
HAVING COUNT(*) > 1

Answer 3

One possibility if your database server supports subqueries:

select * from your_table
 where (StateId, OrderId, OrderTime, PermitId) in
  ( select StateId, OrderId, OrderTime, PermitId
      from your_table
     group by StateId, OrderId, OrderTime, PermitId
    having count(1) > 1 )