MySQL – How to Remove Duplicates in Sequence Based on Keys

duplicationMySQL

I am looking to remove duplicates on a row by row comparison based on keys Member and Group and data sorted ASC on Member, Group and StartDate. Just return the records in the select query and not delete in the table.

I am basically looking at a row by row comparison to eliminate the duplicates based on the 2 keys and value of the Status column with the data sorted by Member, Group and Start_date.

Sample data:

Member  Group   Status        Start_date        End Date
1         2     Active       4/12/2011 01:19    NULL
1         2     Active       4/12/2011 02:19    NULL
1         2     Inactive     4/12/2011 03:19    NULL
1         2     Inactive     4/15/2011 04:19    NULL
1         2     Inactive     4/16/2011 05:19    NULL
1         2     Pending      4/16/2011 11:23    NULL
1         2     Active       4/16/2011 11:25    NULL
1         2     Pending      4/16/2011 11:27    NULL
11        22    Inactive     4/16/2011 11:27    NULL

Expected Output:

Member  Group   Status        Start_date        End Date
1         2     Active       4/12/2011 01:19    NULL
1         2     Inactive     4/12/2011 03:19    NULL
1         2     Pending      4/16/2011 11:23    NULL
1         2     Active       4/16/2011 11:25    NULL
1         2     Pending      4/16/2011 11:27    NULL
11        22    Inactive     4/16/2011 11:27    NULL

DDL and DML:

CREATE TABLE `temp1` (
  `MEMBER_ID` int(10) unsigned NOT NULL,
  `GROUPIE_ID` int(10) unsigned NOT NULL,
  `STATUS` varchar(12) CHARACTER SET utf8 NOT NULL,
  `START_DATE` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (1,2,'Active', '2011-04-12 01:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (1,2,'Active', '2011-04-12 02:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (1,2,'InActive', '2011-04-12 03:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (1,2,'InActive', '2011-04-15 04:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (1,2,'InActive', '2011-04-16 05:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (1,2,'Pending', '2011-04-16 06:23:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (1,2,'Active', '2011-04-16 07:25:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (1,2,'Pending', '2011-04-16 08:27:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (1,2,'InActive', '2011-04-17 09:27:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (11,22,'Active', '2011-04-12 01:19:51 PM', NULL);
Insert into temp1(Member_id, Groupie_id, Status,Start_date, End_Date ) 
values (11,23,'InActive', '2011-04-13 02:19:51 PM', NULL);

Best Answer

This works on consecutive Start_date for each partition (on Member):

SELECT `Member`, `Group`, `Status`, `Start_date`, `End Date`
FROM (
    SELECT @row := CASE WHEN @status=Status AND @member=Member AND @group = `Group`
        THEN @row + 1 ELSE 1 END as row
        , @member:=Member as Member
        , @group:=`Group` as `Group`
        , @status:=Status as Status
        , `Start_date`, `End Date`
    FROM data d
        , (SELECT @row := 0, @status := '', @member := 0, @group := 0) v
    ORDER BY Member, `Group`, Start_date
) as n
WHERE row = 1
ORDER BY Member, Start_date
;

Sample SQL Fiddle.

The behavior of this query is similar to the ROW_NUMBER() available in Oracle (>= 10g), PostgreSQL (>= 8.4) and SQL Server (>= 2012).

Notes on Partitions:

This query and SQL Fiddle partition by Members and order Status by Members and Start_date.
Partition can be remove by removing @member=Member from CASE
Partition can be added or set by both Members and Group like this (SQL Fiddle):
- Add a test on @group = 'group' to the CASE
- Add @group := 'Group' as 'Group' in the inner SELECT [n]
- Add @group := 0 in the variable SELECT [v]
- Add Group to ORDER BY clauses

In term of performance, an index matching the order and columns used in the ORDER BY and CASE is very likely needed.

Output:

Member  | Group     | Status    | Start_date                | End Date
1       | 2         | Active    | April, 12 2011 03:19:00   | (null)
1       | 2         | Inactive  | April, 12 2011 05:19:00   | (null)
1       | 2         | Pending   | April, 16 2011 13:23:00   | (null)
1       | 2         | Active    | April, 16 2011 13:25:00   | (null)
1       | 2         | Pending   | April, 16 2011 13:27:00   | (null)
11      | 22        | Inactive  | April, 16 2011 13:27:00   | (null)

Related Solutions

Mysql – Drop auto increment hack w/o alter table

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

Mysql – remove almost duplicates based on insert time

Here is something very quick and dirty

Get the minimum datetime for each personalid, status, ride_taken

SELECT B.* FROM
(SELECT personalid,status,ride_taken,MIN(swipe_time) swipe_time
FROM swipes GROUP BY personalid,status,ride_taken) A
INNER JOIN swipes B USING (personalid,status,ride_taken,swipe_time);

First let's load your sample data:

mysql> drop database if exists sdujan;
Query OK, 1 row affected (0.00 sec)

mysql> create database sdujan;
Query OK, 1 row affected (0.00 sec)

mysql> use sdujan;
Database changed
mysql> CREATE TABLE `swipes` (
    ->  `id` int(11) NOT NULL AUTO_INCREMENT,
    ->  `personalid` varchar(20) NOT NULL,
    ->  `swipe_time` datetime NOT NULL,
    ->  `status` tinyint(1) NOT NULL COMMENT '1=registered,0=not',
    ->  `ride_taken` tinyint(1) NOT NULL DEFAULT '0',
    ->  `source` tinyint(4) NOT NULL DEFAULT '0',
    ->  PRIMARY KEY (`id`),
    ->  UNIQUE KEY `my_key` (`personalid`,`swipe_time`,`status`,`ride_taken`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into swipes values
    -> (1661,'C08877547','2012-10-21 01:12:08',1,1,3),
    -> (1662,'C09364782','2012-10-21 01:23:38',1,1,3),
    -> (1663,'C09364782','2012-10-21 01:23:48',1,1,3),
    -> (1664,'D09490557','2012-10-21 01:24:39',1,1,3);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from swipes;
+------+------------+---------------------+--------+------------+--------+
| id   | personalid | swipe_time          | status | ride_taken | source |
+------+------------+---------------------+--------+------------+--------+
| 1661 | C08877547  | 2012-10-21 01:12:08 |      1 |          1 |      3 |
| 1662 | C09364782  | 2012-10-21 01:23:38 |      1 |          1 |      3 |
| 1663 | C09364782  | 2012-10-21 01:23:48 |      1 |          1 |      3 |
| 1664 | D09490557  | 2012-10-21 01:24:39 |      1 |          1 |      3 |
+------+------------+---------------------+--------+------------+--------+
4 rows in set (0.00 sec)

mysql>

Here is the output of my proposed query

mysql> SELECT B.* FROM
    -> (SELECT personalid,status,ride_taken,MIN(swipe_time) swipe_time
    -> FROM swipes GROUP BY personalid,status,ride_taken) A
    -> INNER JOIN swipes B USING (personalid,status,ride_taken,swipe_time);
+------+------------+---------------------+--------+------------+--------+
| id   | personalid | swipe_time          | status | ride_taken | source |
+------+------------+---------------------+--------+------------+--------+
| 1661 | C08877547  | 2012-10-21 01:12:08 |      1 |          1 |      3 |
| 1662 | C09364782  | 2012-10-21 01:23:38 |      1 |          1 |      3 |
| 1664 | D09490557  | 2012-10-21 01:24:39 |      1 |          1 |      3 |
+------+------------+---------------------+--------+------------+--------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!

SUGGESTION #1

To accommodate the query better, add this index

ALTER TABLE swipes ADD UNIQUE KEY `my_key2`
(`personalid`,`status`,`ride_taken`,`swipe_time`);

SUGGESTION #2 (Optional)

To accommodate more than 255 swipe stations, perhaps source should be smallint unsigned

SUGGESTION #3 (Optional)

Some Transit Systems allow multiple swipes (up to 4 for PATH TransHudson) in one station. You could give some additional thought on this should you have to allow multiple swipes.

UPDATE 2012-10-24 17:30 EDT

If you are trying to limit within a time range, I have another viewpoint for you

Here is the subquery within my answer:

mysql> SELECT
    ->     personalid,status,ride_taken,MIN(swipe_time) swipe_time
    -> FROM
    ->     swipes GROUP BY personalid,status,ride_taken
    -> ;
+------------+--------+------------+---------------------+
| personalid | status | ride_taken | swipe_time          |
+------------+--------+------------+---------------------+
| C08877547  |      1 |          1 | 2012-10-21 01:12:08 |
| C09364782  |      1 |          1 | 2012-10-21 01:23:38 |
| D09490557  |      1 |          1 | 2012-10-21 01:24:39 |
+------------+--------+------------+---------------------+
3 rows in set (0.00 sec)

mysql>

Let's say you want to limit to the Latest Entry Per 10 seconds. You will first have to generate a ten-second interval boundary like this:

mysql> SELECT personalid,status,ride_taken,swipe_time,
    -> (swipe_time - INTERVAL MOD(UNIX_TIMESTAMP(swipe_time),10) SECOND) ten_sec_in
    -> FROM swipes;
+------------+--------+------------+---------------------+---------------------+
| personalid | status | ride_taken | swipe_time          | ten_sec_int         |
+------------+--------+------------+---------------------+---------------------+
| C08877547  |      1 |          1 | 2012-10-21 01:12:08 | 2012-10-21 01:12:00 |
| C09364782  |      1 |          1 | 2012-10-21 01:23:38 | 2012-10-21 01:23:30 |
| C09364782  |      1 |          1 | 2012-10-21 01:23:48 | 2012-10-21 01:23:40 |
| D09490557  |      1 |          1 | 2012-10-21 01:24:39 | 2012-10-21 01:24:30 |
+------------+--------+------------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

Perhaps the thing to do is introduce the ten_second boundary into the table

ALTER TABLE swipes ADD swipe_int DATETIME;
UPDATE swipes SET
swipe_int = swipe_time - INTERVAL MOD(UNIX_TIMESTAMP(swipe_time),10) SECOND;
ALTER TABLE swipes ADD UNIQUE (personalid,swipe_int);

I ran this

mysql> ALTER TABLE swipes ADD swipe_int DATETIME;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> UPDATE swipes SET
    -> swipe_int = swipe_time - INTERVAL MOD(UNIX_TIMESTAMP(swipe_time),10) SECOND;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> ALTER TABLE swipes ADD UNIQUE personalid_swipe_int_ndx (personalid,swipe_int);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from swipes;
+------+------------+---------------------+--------+------------+--------+---------------------+
| id   | personalid | swipe_time          | status | ride_taken | source | swipe_int           |
+------+------------+---------------------+--------+------------+--------+---------------------+
| 1661 | C08877547  | 2012-10-21 01:12:08 |      1 |          1 |      3 | 2012-10-21 01:12:00 |
| 1662 | C09364782  | 2012-10-21 01:23:38 |      1 |          1 |      3 | 2012-10-21 01:23:30 |
| 1663 | C09364782  | 2012-10-21 01:23:48 |      1 |          1 |      3 | 2012-10-21 01:23:40 |
| 1664 | D09490557  | 2012-10-21 01:24:39 |      1 |          1 |      3 | 2012-10-21 01:24:30 |
+------+------------+---------------------+--------+------------+--------+---------------------+
4 rows in set (0.00 sec)

mysql> show create table swipes\G
*************************** 1. row ***************************
       Table: swipes
Create Table: CREATE TABLE `swipes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `personalid` varchar(20) NOT NULL,
  `swipe_time` datetime NOT NULL,
  `status` tinyint(1) NOT NULL COMMENT '1=registered,0=not',
  `ride_taken` tinyint(1) NOT NULL DEFAULT '0',
  `source` tinyint(4) NOT NULL DEFAULT '0',
  `swipe_int` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `my_key` (`personalid`,`swipe_time`,`status`,`ride_taken`),
  UNIQUE KEY `personalid_swipe_int_ndx` (`personalid`,`swipe_int`)
) ENGINE=MyISAM AUTO_INCREMENT=1665 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql>

Once you do that, going forward, any INSERT within the same 10-second interval gets immediately rejected. That way, you do not have to code it. It will be part of the table design.

Naturally, you will need a trigger to populate swipe_int. Here is the trigger:

mysql> DELIMITER $$
mysql> CREATE TRIGGER swipes_bi BEFORE INSERT ON swipes
    -> FOR EACH ROW
    -> BEGIN
    ->     SET NEW.swipe_int = (NEW.swipe_time -
    ->     INTERVAL MOD(UNIX_TIMESTAMP(NEW.swipe_time),10) SECOND);
    -> END $$
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER ;
mysql>