How to delete duplicate rows when parts of the rows are unique in MySQL?

Question

I have a table where i insert newsitems from a game and timestamp each row on insert.

Problem with this is that each row become unique with the timestamp and i want to remove the duplicates so i only have rows with changes shown.

Anyone know how i can solve this?

table with rows

Here is the create statement for the table

CREATE TABLE `ranklist_alliance_archive` (
  `serverid` int(11) NOT NULL,
  `resetid` int(5) DEFAULT NULL,
  `rank` int(11) NOT NULL,
  `number` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `land` varchar(45) DEFAULT NULL,
  `networth` varchar(45) DEFAULT NULL,
  `tag` varchar(45) DEFAULT NULL,
  `gov` varchar(45) DEFAULT NULL,
  `gdi` varchar(45) DEFAULT NULL,
  `protection` varchar(45) DEFAULT NULL,
  `vacation` varchar(45) DEFAULT NULL,
  `alive` varchar(45) DEFAULT NULL,
  `deleted` varchar(45) DEFAULT NULL,
  `created_ts` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Rows are: serverid, resetid, rank, number, name, land, networth, tag, gov, gdi, protection, vacation, alive, deleted, created_ts

Does this answer your question? [How to delete duplicate rows without unique identifier](https://stackoverflow.com/questions/26769454/how-to-delete-duplicate-rows-without-unique-identifier) — Shahid, Oct 12 '20 at 10:34
It's the same table not 2 different tables so comparing the same table twice makes it all unique. — swi, Oct 12 '20 at 11:10

score 0 · Answer 1 · answered Oct 12 '20 at 10:46

If you want to select such rows where values change, then one method is:

select raa.*
from (select raa.*,
             lag(timestamp) over (order by timestamp) as prev_timestamp,
             lag(timestamp) over (partition by . . . order by timestamp) as prev_timestamp_2
      from ranklist_alliance_archive raa
     ) raa
where prev_timestamp_2 is not null or
      prev_timestamp <> prev_timestamp_2;

The . . . is for the columns where you care about the changes.

If you want to turn this into a delete, then you can use left join. If the timestamp is actually unique, you can use:

delete raa
    from ranklist_alliance_archive raa left join
         (select raa.*
          from (select raa.*,
                       lag(timestamp) over (order by timestamp) as prev_timestamp,
                       lag(timestamp) over (partition by . . . order by timestamp) as prev_timestamp_2
                from ranklist_alliance_archive raa
               ) raa
          where prev_timestamp_2 is not null or
                prev_timestamp <> prev_timestamp_2
         ) raa2
         on raa2.timestamp = raa.timestamp
    where raa2.timestamp is null;

How to delete duplicate rows when parts of the rows are unique in MySQL?

1 Answers1