Method for getting the last record for mysql repeat record

It is common to have a large number of repeated records in a table in an application, but sometimes we want to filter duplicate data and retrieve a record of Repeated Records, next, I will introduce you to a method to retrieve one of the Repeated Records.

See the following table:

The Code is as follows:	Copy code
Create table 't1 '( 'Userid' INT (11) default null, 'Atime' datetime default null, KEY 'idx _ userid' ('userid ') ) ENGINE = InnoDB default charset = utf8;

The data is as follows:

The Code is as follows:	Copy code
MySQL> SELECT * FROM t1; + -------- + --------------------- + | Userid | atime | + -------- + --------------------- + | 1 | 11:05:25 | | 2 | 11:05:29 | | 3 | 11:05:32 | | 5 | 11:05:34 | | 1 | 11:05:40 | | 2 | 11:05:43 | | 3 | 11:05:48 | | 5 | 11:06:03 | + -------- + --------------------- + 8 rows in set (0.00 sec)

The userid is not unique. It is required that the time corresponding to each userid In the table be the closest to the current time. when we first see this condition, we usually think of borrowing a temporary table and adding a primary table using the join operation.
Give a simple method:

The Code is as follows:	Copy code
MySQL> SELECT userid, substring_index (group_concat (atime order by atime DESC), ",", 1) AS atime FROM t1 group by userid; + -------- + --------------------- + | Userid | atime | + -------- + --------------------- + | 1 | 11:05:40 | | 2 | 11:05:43 | | 3 | 11:05:48 | | 5 | 11:06:03 | + -------- + --------------------- + 4 rows in set (0.03 sec)

Query and delete duplicate records

Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid

The Code is as follows:	Copy code
Delete from people Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)

3. Search for redundant duplicate records in the table (multiple fields)

The Code is as follows:	Copy code
Select * from vitae Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)

4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid

The Code is as follows:	Copy code
Delete from vitae Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid

The Code is as follows:	Copy code
Select * from vitae Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)