Menu
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?
883
To select duplicate values, you need to create groups of rows with the same values and then select the groups with counts greater than one. You can achieve that by using GROUP BY and a HAVING clause.
If you do not include DISTINCT in a SELECT clause, you might find duplicate rows in your result, because SQL returns the JOB column's value for each row that satisfies the search condition. Null values are treated as duplicate rows for DISTINCT.
The go to solution for removing duplicate rows from your result sets is to include the distinct keyword in your select statement. It tells the query engine to remove duplicates to produce a result set in which every row is unique.
One way to find duplicate records from the table is the GROUP BY statement. The GROUP BY statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has the same values in different rows then it will arrange these rows in a group.
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 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
