MYSQL removing Duplicate rows based on 3 columns

Question

EDIT the SQL version (mysql 5.7) I have does not support row_number() ... what alternative method can I used?

I researched an found out that using row_number() where is my query would I apply it... the columns I want to check are user_id racid and ios

SELECT 
    ur.user_id,
    u.racid,
    u.fname,
    u.lname,
    u.email,
    u.last_login,
    ur.role_id,
    r.name AS role_name,
    i.device_token AS ios,
    dg.group_name,
    dg.ad_group,
    a.device_token AS android
FROM
    users AS u
        LEFT OUTER JOIN
    user_roles AS ur ON u.id = ur.user_id
        LEFT OUTER JOIN
    roles AS r ON ur.role_id = r.id
        LEFT OUTER JOIN
    ios_tokens AS i ON u.racid = i.racf_id
        LEFT OUTER JOIN
    android_tokens AS a ON u.racid = a.racf_id
        LEFT OUTER JOIN
    dashboard_groups AS dg ON dg.role_id = r.id

Current Table after joining

user_id, racid, fname, lname, email, last_login, role_id, role_name, ios, group_name, ad_group, android

which mysql version you are using – Fahmi Oct 12 '18 at 10:09 — Fahmi, Oct 12 '18 at 10:09
MySQL 5.7 doesn't support `row_number()`. – Gordon Linoff Oct 12 '18 at 10:27 — Gordon Linoff, Oct 12 '18 at 10:27
@GordonLinoff is there another method I can use? – acctman Oct 12 '18 at 10:28 — acctman, Oct 12 '18 at 10:28

Fahmi · Answer 1 · 2018-10-12T10:35:16.827

You can try below it will work for mysql 8+

select * from 
(SELECT 
    ur.user_id,
    u.racid,
    u.fname,
    u.lname,
    u.email,
    u.last_login,
    ur.role_id,
    r.name AS role_name,
    i.device_token AS ios,
    dg.group_name,
    dg.ad_group,
    a.device_token AS android,row_number() over(partition by ur.user_id,u.racid,i.device_token order by ur.user_id,
    u.racid,i.device_token) as rn
FROM
    users AS u
        LEFT OUTER JOIN
    user_roles AS ur ON u.id = ur.user_id
        LEFT OUTER JOIN
    roles AS r ON ur.role_id = r.id
        LEFT OUTER JOIN
    ios_tokens AS i ON u.racid = i.racf_id
        LEFT OUTER JOIN
    android_tokens AS a ON u.racid = a.racf_id
        LEFT OUTER JOIN
    dashboard_groups AS dg ON dg.role_id = r.id
)X where rn<>1

OR Mysql below 8+ version you can try following

select * from 
( SELECT 
            ur.user_id,
            u.racid,
            u.fname,
            u.lname,
            u.email,
            u.last_login,
            ur.role_id,
            r.name AS role_name,
            i.device_token AS ios,
            dg.group_name,
            dg.ad_group,
            a.device_token AS android,
             @row_number:=CASE
            WHEN @user_id = ur.user_id and @racid = u.racid and @evicetoken = i.device_token  THEN @row_number + 1
            ELSE 1
        END AS num,
        @user_id :=ur.user_id, @racid = u.racid, @evicetoken = i.device_token 
        FROM
            users AS u
                LEFT OUTER JOIN
            user_roles AS ur ON u.id = ur.user_id
                LEFT OUTER JOIN
            roles AS r ON ur.role_id = r.id
                LEFT OUTER JOIN
            ios_tokens AS i ON u.racid = i.racf_id
                LEFT OUTER JOIN
            android_tokens AS a ON u.racid = a.racf_id
                LEFT OUTER JOIN
            dashboard_groups AS dg ON dg.role_id = r.id,
        (SELECT @user_id:=0,@racid=0,@evicetoken=0,@row_number:=0) as t
)X where num<>1

I received this error: `Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by ur.user_id,u.racid,i.device_token order by ur.user_id, u.racid' at line 14` — acctman, Oct 12 '18 at 10:24
yes, according to your version it will not work, I've updated answer for mysql lower version you can tey that — Fahmi, Oct 12 '18 at 10:31
@modified but if you get error again then it will be great if you can share your table structure as you know your query is very long so it's tough to identify error based on assumption — Fahmi, Oct 12 '18 at 10:36
returned the follow: `0 row(s) returned` I have added the table field names too my original question. — acctman, Oct 12 '18 at 10:49

score 0 · Answer 2 · answered Oct 12 '18 at 10:43

0

This is not about removing duplicate rows but if you need uniqueness from single table for multiple columns you can use unique index with multiple columns. If this is not you need kindly ignore this.

ALTER TABLE `users` ADD UNIQUE `unique_index`(`col1`, `col2`, `col3`);

How do I specify unique constraint for multiple columns in MySQL?

answered Oct 12 '18 at 10:43

Krishnadas PC

3,966
34
33

hmm i tried appending the ALTER statement to the end of my current statement but no luck – acctman Oct 12 '18 at 11:28
This is to set constraint so that after this no more duplicates will enter. – Krishnadas PC Oct 12 '18 at 11:39

MYSQL removing Duplicate rows based on 3 columns

2 Answers2