Question 20015 in Mysql Question posted in
The official MySQL documentation can be found here.

Delete duplicated rows if previous date has the same value – Mysql

I have the following table structure:

+----------+------------+------+---------------------+---------------------+
| id       | username   | elo  | created_at          | updated_at          |
+----------+------------+------+---------------------+---------------------+
| 58433707 | HEADCRACK- | 1408 | 2023-03-22 00:30:07 | 2023-03-22 00:30:07 |
| 58492411 | HEADCRACK- | 1408 | 2023-03-23 00:08:30 | 2023-03-23 00:08:30 |
| 58548133 | HEADCRACK- | 1408 | 2023-03-24 00:12:13 | 2023-03-24 00:12:13 |
| 58605834 | HEADCRACK- | 1408 | 2023-03-25 01:14:58 | 2023-03-25 01:14:58 |
| 58659285 | HEADCRACK- | 1408 | 2023-03-26 01:19:59 | 2023-03-26 01:19:59 |
| 58707509 | HEADCRACK- | 1200 | 2023-03-27 00:55:10 | 2023-03-27 00:55:10 |
| 58780204 | HEADCRACK- | 1200 | 2023-03-28 04:54:07 | 2023-03-28 04:54:07 |
| 58825272 | HEADCRACK- | 1200 | 2023-03-29 00:09:23 | 2023-03-29 00:09:23 |
| 58884438 | HEADCRACK- | 1200 | 2023-03-30 00:08:07 | 2023-03-30 00:08:07 |
| 58945645 | HEADCRACK- | 1200 | 2023-03-31 00:15:42 | 2023-03-31 00:15:42 |
| 59003269 | HEADCRACK- | 1111 | 2023-04-01 00:12:30 | 2023-04-01 00:12:30 |
| 59062643 | HEADCRACK- | 1111 | 2023-04-02 00:01:58 | 2023-04-02 00:01:58 |
| 59124972 | HEADCRACK- | 1111 | 2023-04-03 00:07:38 | 2023-04-03 00:07:38 |
| 59189072 | HEADCRACK- | 1111 | 2023-04-04 01:01:39 | 2023-04-04 01:01:39 |
| 59244220 | HEADCRACK- | 1111 | 2023-04-05 00:12:18 | 2023-04-05 00:12:18 |
| 59306298 | HEADCRACK- | 1111 | 2023-04-06 00:04:55 | 2023-04-06 00:04:55 |
| 59368164 | HEADCRACK- | 1111 | 2023-04-07 00:10:33 | 2023-04-07 00:10:33 |
| 59368169 | HEADCRACK- | 1111 | 2023-04-07 00:10:34 | 2023-04-07 00:10:34 |
| 59368171 | HEADCRACK- | 1408 | 2023-04-07 00:10:35 | 2023-04-07 00:10:35 |
| 59368177 | HEADCRACK- | 1408 | 2023-04-07 00:10:35 | 2023-04-07 00:10:35 |
| 59368186 | HEADCRACK- | 1408 | 2023-04-07 00:10:36 | 2023-04-07 00:10:36 |
| 59423543 | HEADCRACK- | 1408 | 2023-04-08 00:06:49 | 2023-04-08 00:06:49 |
| 59491244 | HEADCRACK- | 1111 | 2023-04-09 01:04:45 | 2023-04-09 01:04:45 |
| 59491246 | HEADCRACK- | 1111 | 2023-04-09 01:04:45 | 2023-04-09 01:04:45 |
| 59548528 | HEADCRACK- | 1111 | 2023-04-10 00:17:08 | 2023-04-10 00:17:08 |
| 59604853 | HEADCRACK- | 1111 | 2023-04-11 02:13:34 | 2023-04-11 02:13:34 |
| 59656442 | HEADCRACK- | 1111 | 2023-04-12 01:34:44 | 2023-04-12 01:34:44 |
| 59706976 | HEADCRACK- | 1555 | 2023-04-13 01:16:24 | 2023-04-13 01:16:24 |
| 59761891 | HEADCRACK- | 1555 | 2023-04-14 00:11:26 | 2023-04-14 00:11:26 |
| 59761899 | HEADCRACK- | 1555 | 2023-04-14 00:11:27 | 2023-04-14 00:11:27 |
| 59833256 | HEADCRACK- | 1555 | 2023-04-15 03:53:51 | 2023-04-15 03:53:51 |
| 59881273 | HEADCRACK- | 1555 | 2023-04-16 01:00:00 | 2023-04-16 01:00:00 |
| 59881274 | HEADCRACK- | 1555 | 2023-04-16 01:00:00 | 2023-04-16 01:00:00 |
| 59939150 | HEADCRACK- | 1555 | 2023-04-17 02:24:15 | 2023-04-17 02:24:15 |
| 59955527 | HEADCRACK- | 1555 | 2023-04-18 14:12:33 | 2023-04-18 14:12:33 |
| 60000472 | HEADCRACK- | 1555 | 2023-04-19 02:03:40 | 2023-04-19 02:03:40 |
| 60066172 | HEADCRACK- | 1555 | 2023-04-20 00:03:53 | 2023-04-20 00:03:53 |
+----------+------------+------+---------------------+---------------------+

Those are ELO entries for one specific user on a daily basis. As you can see, the user has multiple entries for different dates even though the ELO rating is the same. I would like to clear the database, for each user, and delete rows with the same elo value for previous days/dates. The table above should look like this:

+----------+------------+------+---------------------+---------------------+
| id       | username   | elo  | created_at          | updated_at          |
+----------+------------+------+---------------------+---------------------+
| 58659285 | HEADCRACK- | 1408 | 2023-03-26 01:19:59 | 2023-03-26 01:19:59 |
| 58945645 | HEADCRACK- | 1200 | 2023-03-31 00:15:42 | 2023-03-31 00:15:42 |
| 59368169 | HEADCRACK- | 1111 | 2023-04-07 00:10:34 | 2023-04-07 00:10:34 |
| 59423543 | HEADCRACK- | 1408 | 2023-04-08 00:06:49 | 2023-04-08 00:06:49 |
| 59656442 | HEADCRACK- | 1111 | 2023-04-12 01:34:44 | 2023-04-12 01:34:44 |
| 60066172 | HEADCRACK- | 1555 | 2023-04-20 00:03:53 | 2023-04-20 00:03:53 |
+----------+------------+------+---------------------+---------------------+

A lot of users have just a few duplicate elo entries while others have different elo entries every day. I just want to clear the duplicate entires. Does someone know how I could achieve this?

Tags:

3

Answers


  1. 0

    You can use partitions and then to filter row_num=1

    In your sentence SELECT

    SELECT * FROM (
    SELECT columns, ROW_NUMBER() OVER(PARTITION BY elo ORDER BY created_at) AS row_num
    FROM table
) t
WHERE row_num=1

    Or with CTE

    WITH tablaCTE AS (
    SELECT columns, ROW_NUMBER() OVER(PARTITION BY elo ORDER BY created_at) AS row_num
    FROM table
)
SELECT *
FROM tableCTE
WHERE row_num=1
    Login or Signup to reply.
  2. 0

    You can use a partition with a ROW_NUMBER() which can reset when either the player or the elo changes.

    WITH CTE_RANKED AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY username, elo ORDER BY created_at DESC) AS row_num
    FROM {{ YOURTABLE }}
) t
SELECT id, username, elo, created_at, updated_at
FROM CTE_RANKED
WHERE row_num = 1

    Notice the ordering ASC vs DESC will determine if you keep the first or the last elo recorded when you are only looking at 1 of them

    Login or Signup to reply.
  3. 0

    This is my first post here.
    Maybe this is what you want:

    DELETE t1
FROM table t1
INNER JOIN table t2
  ON t1.username = t2.username
  AND t1.elo = t2.elo
  AND t1.created_at < t2.created_at

    Note that you should always backup your database/tables before running any queries that modify or delete data.

    Also note, that this query is based on created_at condition, not updated_at, you can change condition to this at last line:

    AND t1.updated_at < t2.updated_at
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search