CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-groupmysql-8.0
kari
kari

Reputation: 23

How to remove duplicate data in MySQL

i have table user like this

UserID   | UserName | UserEmail       | FlagUser
1        | Penny    | [email protected] | A1
2        | Maika    | [email protected] | A1
3        | Laila    | [email protected] | A1
4        | Laura    | [email protected] | A2
5        | Penny    | [email protected] | A2
6        | Maika    | [email protected] | A2

i want the result like this, if user has more than one data i want get the user with FlagUser A2

UserID   | UserName | UserEmail       | FlagUser
3        | Laila    | [email protected] | A1
4        | Laura    | [email protected] | A2
5        | Penny    | [email protected] | A2
6        | Maika    | [email protected] | A2

Upvotes: 2

Views: 124

Answers (4)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

If you are using mysql 8.0 then row_number() WITH Common table expression is the convenient way:

Schema:

 create table user(UserID int, UserName varchar(50), UserEmail varchar(50), FlagUser varchar(50));
 insert into user values(1,        'Penny'    , '[email protected]'  ,'A1');
 insert into user values(2,        'Maika'    , '[email protected]'  ,'A1');
 insert into user values(3,        'Laila'    , '[email protected]'  ,'A1');
 insert into user values(4,        'Laura'    , '[email protected]'  ,'A2');
 insert into user values(5,        'Penny'    , '[email protected]'  ,'A2');
 insert into user values(6,        'Maika'    , '[email protected]'  ,'A2');

Query

 with cte as
 (
     select *,row_number()over(partition by username order by flaguser desc) rn
     from user
 )
 select * from cte where rn=1

Output:

UserID UserName UserEmail FlagUser rn
3 Laila [email protected] A1 1
4 Laura [email protected] A2 1
6 Maika [email protected] A2 1
5 Penny [email protected] A2 1

db<>fiddle here

You can also have the same result with Inner join and group by clause:

 select u.* from user u
 inner join
 (select username,max(flaguser) maxflaguser from user
 group by username) u2
 on u.username=u2.username and u.flaguser=u2.maxflaguser

Output:

UserID UserName UserEmail FlagUser
3 Laila [email protected] A1
4 Laura [email protected] A2
5 Penny [email protected] A2
6 Maika [email protected] A2

Or you can just use subquery to select single row for each user with highest value of flaguser:

 Select * from user u
 where flaguser=(select max(flaguser) from user u2 where u.username=u2.username)

Output:

UserID UserName UserEmail FlagUser
3 Laila [email protected] A1
4 Laura [email protected] A2
5 Penny [email protected] A2
6 Maika [email protected] A2

db<>fiddle here

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If you have only the two flags and an index on username, flaguser, then the fastest method might be:

select u.*
from users u
where u.flaguser = 'A2' or
      not exists (select 1
                  from users u2
                  where u2.name = u.name and u2.flaguser = 'A2'
                 );

Upvotes: 0

Akina
Akina

Reputation: 42612

DELETE t1
FROM user t1
JOIN user t2 USING (UserName)
WHERE t1.FlagUser = 'A1'
  AND t2.FlagUser = 'A2'

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

I'm not convinced that the requirement is entirely clear, but I guess you're after something like this:

SELECT x.* 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.username = x.username 
   AND y.flaguser = 'A2' 
   AND y.user_id <> x.user_id 
 WHERE y.user_id IS NULL;

Upvotes: 1

Related Questions