Issue using MIN() in mysql query

Question

I made the following query that get the MIN(id) of duplicate path values in the table movies:

SELECT m.id, m.user_id , m.path
FROM movies m
  INNER JOIN (
               SELECT Min(id) as movie_id, path,  COUNT(*)
               FROM movies
               WHERE importer LIKE '%AS%'
               GROUP BY  path
               HAVING COUNT(*) > 1) temp
    ON  temp.path = p.path
        AND temp.movie_id = m.id
        AND importer LIKE '%AS%'

I got this result:

  id       | user_id |     path    | 
------------------------------------
 2         | 1234    |     XXXX    |  
 8         | 4231    |     BBBB    |

The problem is that I have another movie with the same path but dont appear because the MIN() and the GROUP BY path

I want to group the path but dont exlude the other path because I do MIN(id)

This is the expected result:

  id       | user_id |     path    | 
------------------------------------
 2         | 1234    |     XXXX    |  
 8         | 4231    |     BBBB    | 
 5         | 3421    |     BBBB    |

What Im doing wrong?

@Tim Biegeleisen

The problem is with that query I obtain duplicated paths with same user_id :

  id       | user_id |     path    | 
------------------------------------
  3523        12287         asd
  3524        12287         asd
  3525        12287         asd

I should obtain the min id for each grouped path.

Surely you would want temp.importer to equal p.importer!! Beyond that, see meta.stackoverflow.com/questions/333952/… — Strawberry
– Strawberry, Commented Feb 7, 2017 at 16:21
@user3741598 Be aware that DISTINCT is not a function - it takes no arguments. — Strawberry
– Strawberry, Commented Feb 7, 2017 at 16:34

Tim Biegeleisen · Accepted Answer · 2017-02-07 16:42:25Z

1

I think what you want to do is to GROUP BY the user_id and path, and then choose the record having the smallest movie ID, should there be multiple users associated with a given path. If so, then the following query should work:

SELECT m1.id, m1.user_id, m1.path
FROM movies m1
INNER JOIN
(
    SELECT user_id, path, MIN(id) AS min_id
    FROM movies
    WHERE importer LIKE '%AS%'
    GROUP BY user_id, path
) m2
    ON m1.path    = m2.path AND
       m1.user_id = m2.user_id AND
       m1.id      = m2.min_id

edited Feb 7, 2017 at 16:42

answered Feb 7, 2017 at 16:22

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

2 Comments

Tim Biegeleisen Over a year ago

@devtreat I updated my answer. I think you want to GROUP BY the user ID and path, and then retain the record with the minimum ID.

Funereal Over a year ago

This query made what I wanted. Thank you very much!

Collectives™ on Stack Overflow

Issue using MIN() in mysql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related