MySQL – Find Longest Sequence Without Duplicates

MySQL

I have database with Id – autoincrement, and Text- varchar.

My database is simple:

+---+--------+----+----+
| Id| Text   |Col1|Col2|
+---+--------+----+----+
| 1 | adf    |1   |0   |
| 2 | qwer   |5   |1   |
+---+--------+----+----+

This harder than I thought. What I want to do is to cut the table at some record and delete the tail.

Reason for this is that I had unique column that at some point in time I started receiving duplicates (because i changed key UNIQUE(Text) into UNIQUE(Text, Id))

Rob Farley's answer was what I asked originally in question but it wasn't what i really needed. I tried a revised version based on Rob's answer but results are still wrong:

SELECT MIN(Id) AS SmallestId, Text FROM table 
GROUP BY Text HAVING COUNT(*) <2 
ORDER BY SmallestId DESC

Problem with Rob's answer is that if first record (Id=1) is duplicate that lowest Id will equal 1 and i'd have to cut whole table.

Problem with my way is that it reports last (highest) occurrence of duplicate so I would have to leave some duplicates inside after the cut.

I think that what I want in human language is:

Find a longest sequence that doesn't have duplicates inside, starting from Id 1 and ordered by Id ASC.

It's a side project now so there is no pressure on answering, reason might be to learn more SQL. I'm the asker of original question but I had to edit as another because I accidentally created two accounts.

Best Answer

You want the smallest id for each Text that appears multiple times.

SELECT MIN(id) AS SmallestId, Text 
FROM Table 
GROUP BY Text
HAVING COUNT(*) > 1;

Edit: Now that you've changed your question...

What about deleting later instances of the same text?

DELETE t
FROM Table t
WHERE EXISTS (SELECT *
    FROM Table t2
    WHERE t2.Text = t.Text
    AND t2.Id < t.Id);

This should leave you with just the smallest Id for each text. It doesn't answer what you're asking, but is hopefully closer to what you need.

If you have anything referring to this table, you will need to keep a copy of the rows you delete.

Related Solutions

Mysql – Regularly import tab-seperated log file to MySQL

Sure, a simple script can parse the results of the tool and insert rows into the database.

You can make composite unique keys in mysql:

 CREATE index foo (mac, time);

If you try to insert a dup, it will fail (but will you ever have a duplicate time?).

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>