SQL Query for "almost" duplicate entries (same values for last 3 columns)

Question

I need help with an SQL-Query. I have one table with many entries and I want to query all the entries which have the same values for the last 3 columns.

My table looks as follows:

|Refrigator|98C08A|2011-08-06 00:00:30|126|126
|Refrigator|B7BE29|2011-08-06 00:00:30|73|70
|Refrigator|599393|2011-08-06 00:00:30|126|126
|Refrigator|B7BE29|2011-08-06 00:00:29|73|70
|Refrigator|599393|2011-08-06 00:00:29|126|126
|Refrigator|599393|2011-08-06 00:00:29|126|126
|Refrigator|98C08A|2011-08-06 00:00:29|126|126
|Refrigator|98C08A|2011-08-06 00:00:29|126|126
|Refrigator|599393|2011-08-06 00:00:28|126|126

So I want to get all rows, which have the exact same values for the last 3 columns, so the result should look like:

|Refrigator|98C08A|2011-08-06 00:00:30|126|126
|Refrigator|599393|2011-08-06 00:00:30|126|126
|Refrigator|599393|2011-08-06 00:00:29|126|126
|Refrigator|599393|2011-08-06 00:00:29|126|126 (if possible without this duplicate)
|Refrigator|98C08A|2011-08-06 00:00:29|126|126
|Refrigator|98C08A|2011-08-06 00:00:29|126|126 (if possible without this duplicate)

Does anyone have an idea how to manage this? What I tried so far was:

SELECT * 
FROM smtab 
WHERE Datetime IN (
      SELECT Datetime 
      FROM smtab 
      GROUP BY Datetime 
      HAVING count(Datetime) >1) 
AND Power1 IN (
      SELECT Power1 
      FROM smtab 
      GROUP BY Power1 
      HAVING count(Power1) >1) 
AND Power8 IN (
      SELECT Power8 
      FROM smtab 
      GROUP BY Power8 
      HAVING count(Power8) >1) 
ORDER BY Datetime DESC;

but I didn't work!!!

Hope someone can help me! thx in advance...

The rows you give in your example result do not have identical values for the last 3 columns. — liquorvicar, May 29 '12 at 19:08
This related article might be of interest http://beemerguy.net/blog/post/How-to-remove-duplicate-SQL-table-entries-(by-example).aspx — BeemerGuy, May 29 '12 at 19:09

score 1 · Accepted Answer · answered May 29 '12 at 19:08

1

SELECT DISTINCT *
FROM   smtab NATURAL JOIN (
  SELECT   Datetime, Power1, Power8
  FROM     smtab
  GROUP BY Datetime, Power1, Power8
  HAVING   COUNT(*) > 1
) AS t

answered May 29 '12 at 19:08

eggyal

122,705
18
212
237

Wow, thx a lot! This is a very elegant solution and works like a charm! – funkypopcorn May 29 '12 at 19:25
This worked perfect for my above mentioned scenario! Now I have another constraint which restricts the number of duplicates which should be replied even further (to a certain limited timespan). So I need all duplicates where for example: Where Datetime > '2011-08-06 00:00:00'' AND Datetime < '2011-08-07 00:00:50' I tried several modifications with your solution, but I couldn't get it to work as expected. :( Any idea how I can manage that? – funkypopcorn Jun 11 '12 at 12:06
@funkypopcorn: Add a `WHERE` clause to the subquery-e.g. `... FROM smtab WHERE Datetime BETWEEN '2011-08-06 00:00:00' AND '2011-08-07 00:00:50' GROUP BY ...`? – eggyal Jun 11 '12 at 12:10
ahhhh thx a lot, I always put the Where-clause at the wrong position! :) – funkypopcorn Jun 11 '12 at 12:28
sry for asking again but I'm really lacking db-knowledge or sql-knowledge and things get more and more complicated. -> Maybe you can also help me with the following follow-up question: http://stackoverflow.com/q/11072721/1424439 – funkypopcorn Jun 17 '12 at 17:25

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

I believe you are looking for a self-join. Take a look at this SO answer to get started. You don't mention which columns you wish to exclude so I can't really provide any code.

edited May 23 '17 at 12:23

Community

1
1

answered May 29 '12 at 19:07

m.edmondson

30,382
27
123
206

Ben · Answer 3 · 2012-05-29T19:13:44.373

Your problem is that you need to identify the duplicates, the inner join, then find everything that matches.

The distinct enables only one of each duplicate to be returned.

-- only select one of each duplicate.
select distinct *
  from smtab as a
        -- Find the duplicates
  join ( select datetime, power1, power8
           from smtab
          group by datetime, power1, power8
         having count(*) > 1) as b
      -- join back on to the main table
    on a.datetime = b.datetime
   and a.power1 = b.power1
   and a.power8 = b.power8

You're looking for a duplicate across all 3 columns, rather than each individually. Hence you have to group by all 3 simultaneously to find your duplicates.

score 0 · Answer 4 · edited May 29 '12 at 19:38

This approach works in my data model, with SQL Server. Not sure if it works with MySQL. I'm joining a table to a derived query. The derived query finds all records that have > 1 record.

select * from Employees as e
 inner join
(
select LastName, firstname from Employees 
 group by LastName, FirstName having COUNT(1) > 1
) as derived
on e.LastName = derived.lastname and e.FirstName = derived.firstname
order by e.LastName

EDIT: To make it more related to your data model, try something like this:

SELECT * FROM smtab as s
  inner join 
(
  select datetime, power1, power8 
    from smtab as s2
   group by s2.datetime, power1, POWER8 having COUNT(1) > 1
) as derived
on s.datetime = derived.datetime and s.power1 = derived.power1 
and s.power8 = derived.power8
ORDER BY Datetime DESC;

SQL Query for "almost" duplicate entries (same values for last 3 columns)

4 Answers4