devtreat devtreat - 1 year ago 81
MySQL Question

Issue using MIN() in mysql query

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.

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download