Remove same values rows over different column mysql

Question

I 'm stuck on a very basic problem, I want to skip the row which has duplicate values over three columns.

Table feeds

id,type,user_id,friend_id,date
1, 'friend', 4 , 5 , 5/5/2010
2, 'friend', 5 , 4 , 5/5/2010

Now this is how the data is saved (I can't change the saving module)

since both have same thing, so I want to pick them only as a 1 row not 2. I don't want to validate and remove it at PHP end, b/c if I'll do at PHP the pagination would be disturb

Edit:

Table Structure

create table `feed` (
    `id` double ,
    `type` blob ,
    `user_id` double ,
    `type_id` double ,
    `date` datetime 
); 
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('78','friends','1314','1313','2012-09-03 19:48:14');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('79','friends','1313','1314','2012-09-03 19:48:14');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('80','friends','1314','1312','2012-09-03 19:49:07');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('82','friends','1313','1312','2012-09-03 19:49:09');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('84','friends','1315','1312','2012-09-03 19:49:24');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('86','friends','1315','1313','2012-09-03 19:49:33');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('87','friends','1313','1315','2012-09-03 19:49:33');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('97','friends','1317','1312','2012-09-03 19:55:06');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('99','friends','1313','1317','2012-09-03 19:56:01');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('100','friends','1317','1313','2012-09-03 19:56:01');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('101','friends','1315','1317','2012-09-03 19:56:58');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('102','friends','1317','1315','2012-09-03 19:56:58');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('104','following','1313','1193','2012-09-03 19:59:39');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('105','following','1313','1308','2012-09-03 19:59:51');
insert into `feed` (`id`, `type`, `user_id`, `type_id`, `date`) values('106','following','1313','1098','2012-09-03 19:59:58');

do you want to eliminate same friendship records? (like in given records "5 is friend of 4" means "4 is friend of 5") — mostar
– mostar, Commented Sep 20, 2012 at 20:52
yes I want to eliminate the same friendship values, b/c 4->5 and 5->4 are same — MZH
– MZH, Commented Sep 20, 2012 at 20:57
If an answer solved your problem, please mark it as accepted by clicking the green button. — Zar
– Zar, Commented Sep 20, 2012 at 21:06

mostar · Accepted Answer · 2012-09-20 21:26:20Z

1

And here is the ultimate solution! If the same friendship pair (reversed) exists it only takes the one where user_id>friend_id.

SELECT DISTINCT type, user_id, friend_id, date 
FROM table t1 
WHERE t1.user_id > t1.friend_id 
    OR NOT EXISTS (
        SELECT * FROM table t2 
            WHERE t1.type=t2.type AND t1.date=t2.date 
            AND t2.user_id = t1.friend_id AND t2.friend_id = t1.user_id 
    )

answered Sep 20, 2012 at 21:26

mostar

4,8612 gold badges30 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lathan · Accepted Answer · 2012-09-20 20:45:37Z

1

The function you are looking for is DISTINCT(). That allows you to group the table by that column and remove duplicates.

Just make sure in your select statement that you also select the other field columns as well:

SELECT id, DISTINCT(type), user_id, friend_id, date FROM TABLENAME

answered Sep 20, 2012 at 20:45

Lathan

8534 silver badges14 bronze badges

4 Comments

Zar Over a year ago

Taking his example, user_id aren't equal in both rows, hence they won't get "merged" with your code as he wants?

MZH Over a year ago

No this distinct doesn't work in this case , see this when I run query screencast.com/t/eYm3JWNrXPCp

Lathan Over a year ago

@Zar, it should grab 1 of the user_ids.

Lathan Over a year ago

@MajinVegeta: Hm... you might need to add GROUP BY type between the WHERE and ORDER clause.

Zar · Accepted Answer · 2012-09-20 21:00:19Z

1

You could perform a JOIN on the table itself, which should do what you are looking for. Something like this might do the trick:

SELECT * FROM tableName a
JOIN tableName b ON a.user_id = b.friend_id

edited Sep 20, 2012 at 21:00

answered Sep 20, 2012 at 20:51

Zar

6,93210 gold badges56 silver badges76 bronze badges

1 Comment

mostar Over a year ago

won't work. it does not even take distinct type and date columns.

mostar · Accepted Answer · 2012-09-20 21:20:03Z

0

You should get rid of those duplicate records by running:

DELETE FROM table t1 WHERE
  EXISTS (
    SELECT * FROM table t2 
    WHERE t1.type=t2.type AND t1.date=t2.date 
      AND t2.user_id = t1.friend_id AND t2.friend_id = t1.user_id 
      AND t1.user_id > t2.user_id 
  )

Then you can get distinct friendship records with:

SELECT DISTINCT type, user_id, friend_id, date FROM table

Also, you can write a DB trigger that fires on INSERT, that checks if a duplicate (with reversed user_id and friend_id) already exists. If exists it does ROLLBACK, else allow INSERT.

answered Sep 20, 2012 at 21:20

mostar

4,8612 gold badges30 silver badges45 bronze badges

Collectives™ on Stack Overflow

Remove same values rows over different column mysql

4 Answers 4

Comments

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related