MySQL unique rows

Question

I need a unique rows without duplications,I want to find the couple(pair of actors) with the largest mutual appearances. for example:

my output:

            +----------+-----------+
            |actor1_id | actor2_id | 
            +----------+-----------+
            | 27       |    60     | 
            | 60       |    27     | 
            +----------+-----------+

I need:

            +----------+-----------+
            |actor1_id | actor2_id | 
            +----------+-----------+
            | 27       |    60     | 
            +----------+-----------+

This is the code:

SELECT T3.actor1_id ,T3.actor2_id 
FROM    actor,
        (SELECT T1.actor_id AS actor1_id , T2.actor_id AS actor2_id, COUNT(T1.film_id) AS mutual_films
        FROM(
             SELECT film_actor.film_id AS film_id , film_actor.actor_id AS actor_id
             FROM film_actor) AS T1 ,
             (
             SELECT film_actor.film_id AS film_id , film_actor.actor_id AS actor_id
             FROM film_actor) AS T2
        WHERE T1.film_id = T2.film_id AND T1.actor_id != T2.actor_id 
        GROUP BY T1.actor_id , T2.actor_id) AS T3

WHERE T3.mutual_films = 
                        (SELECT MAX(T3.mutual_films) AS max_mutual
                        FROM
                                (SELECT T1.actor_id AS actor1_id , T2.actor_id AS actor2_id, COUNT(T1.film_id) AS mutual_films
                                FROM(
                                     SELECT film_actor.film_id AS film_id , film_actor.actor_id AS actor_id
                                     FROM film_actor) AS T1 ,
                                     (
                                     SELECT film_actor.film_id AS film_id , film_actor.actor_id AS actor_id
                                     FROM film_actor) AS T2
                                WHERE T1.film_id = T2.film_id AND T1.actor_id != T2.actor_id 
                                GROUP BY T1.actor_id , T2.actor_id) AS T3)

AND T3.actor1_id != T3.actor2_id
GROUP BY T3.actor1_id , T3.actor2_id

antesoles · Accepted Answer · 2016-12-05 10:58:44Z

2

You could use a UNION statement to combine the 2 columns into one and then make a DISTINCT SELECT on them. Something like:

SELECT DISTINCT comb.actor_id FROM (
    SELECT s1.actor1_id AS actor_id FROM source_table AS s1
    UNION
    SELECT s2.actor2_id AS actor_id FROM source_table AS s2
) AS comb

Would result in

+---------+
|actor_id |
+---------+
| 27      | 
| 60      | 
+---------+

And now you can use your JOINs on the derived table.

answered Dec 5, 2016 at 10:58

antesoles

6836 silver badges21 bronze badges

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

2 Comments

Audais Over a year ago

No maybe I will have more than couple , so I have to get a 2 columns of actors

antesoles Over a year ago

You mean you can have something like actor1_id, actor2_id, actor3_id etc.? Undefined amount of columns with actor_ids? Or undefined amount of rows? Rows wouldn't matter in this attempt. You can have hundreds of them.

yaitloutou · Accepted Answer · 2016-12-05 20:10:17Z

1

Just use < instead of != in the WHERE clause:

T3.actor1_id < T3.actor2_id

answered Dec 5, 2016 at 20:10

yaitloutou

1,79920 silver badges24 bronze badges

Comments

Ullas · Accepted Answer · 2016-12-05 20:03:56Z

1

Just swap the values between 2 columns using a CASE expression. Then find the distinct rows.

Query

select t.`actor1_id_sub` as `actor1_id`,
t.`actor2_id_sub` as `actor2_id` from(
  select `actor1_id`, `actor2_id`,
  case when `actor1_id` < `actor2_id` then `actor1_id`
    else `actor2_id` end as `actor1_id_sub`,
  case when `actor1_id` < `actor2_id` then `actor2_id` 
    else `actor1_id` end as `actor2_id_sub`
  from `actors`
)t
group by t.`actor1_id_sub`, t.`actor2_id_sub`;

SQL Fiddle Demo

edited Dec 5, 2016 at 20:03

answered Dec 5, 2016 at 10:58

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Comments

pratik garg · Accepted Answer · 2016-12-05 10:59:40Z

0

This can be achieve using not exists, like below -

SELECT *
  FROM film_actor t1
 WHERE NOT EXISTS (SELECT 1
          FROM film_actor t2
         WHERE t1.actor2_id = t2.actor1_id
           AND t1.actor1_id = t2.actor2_id
           AND t1.actor1_id > t1.actor2_id)

answered Dec 5, 2016 at 10:59

pratik garg

3,3421 gold badge19 silver badges21 bronze badges

Comments

maslan · Accepted Answer · 2016-12-05 11:04:08Z

0

There is only one way I can think of: Create another field in your table and store both ids in the same order (smaller first or bigger first) in that field. So your table will look like this

+----------+-----------+-----------+
|actor1_id | actor2_id | actor_ids |
+----------+-----------+-----------+
| 27       |    60     |   27-60   |
| 60       |    27     |   27-60   |
+----------+-----------+-----------+

And create your SELECT DISTINCT statement by using actor_ids row.

answered Dec 5, 2016 at 11:04

maslan

463 bronze badges

Comments

Manoj Sharma · Accepted Answer · 2016-12-05 11:15:11Z

0

Please check this, May your problem get resolved with this query.

SELECT test_status, system_generated FROM test_details WHERE test_status <= system_generated 
UNION 
SELECT system_generated, test_status FROM test_details WHERE system_generated < test_status

answered Dec 5, 2016 at 11:15

Manoj Sharma

1,4552 gold badges13 silver badges20 bronze badges

Collectives™ on Stack Overflow

MySQL unique rows

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related