How to show a filtered result ONCE in a MySql query

Question

So here is my dilemma. I want to show the most RECENT image submissions to my gallery. The problem is that sometimes the most recent submissions are by the same person. I'd prefer for a person to show up only once, since sometimes most people upload more than one photo at a time.

For Reference My Tables Are (simplified)

Users
user_id, username

Gallery
media_id, user_id, filename, date_added

This is my current query:

SELECT g.*
     , u.user_id
     , u.fullname AS real_name 
  FROM gallery g 
  LEFT 
  JOIN users u 
    ON u.user_id = g.user_id 
 WHERE g.status = 'approved' 
 ORDER 
    BY g.date_added DESC 
 LIMIT 10

So I get things like:

user_id: 9000 7383idsj39390.jpg
user_id: 9000 sdujnfsd83ss2.jpg
user_id: 829  sdfs3dgsdfsd3.jpg
user_id: 1000 dsfsdfsd34523.jpg

Ideally, I'd like for the user to be unique users.

user_id: 9000 7383idsj39390.jpg
user_id: 829  sdfs3dgsdfsd3.jpg
user_id: 1000 dsfsdfsd34523.jpg
user_id: 500  29928sdksdui2.jpg

I really have no idea how to approach this. Any help would be appreciated!

@TimBiegeleisen is the first one top left http://winterbash2016.stackexchange.com/ — Juan Carlos Oropeza, Dec 20 '16 at 15:10
The referenced duplicate answer doesn't seem to make sense to me. It is confusing as to which ID the person it assumes that the ID is inside the reference table. Mine is a bit different. Updated question to reflect the TABLE structure I'm using. — Hugo, Dec 20 '16 at 19:21

score 0 · Answer 1 · answered Dec 20 '16 at 15:04

If you can accept simply taking the lexicographically largest image file name from each user's records, then you can use a subquery along with GROUP BY to identify the records you want to appear in your result set. Something like this should work:

SELECT g.*,
       u.user_id,
       u.fullname AS real_name
FROM Gallery g
LEFT JOIN Users u
    ON u.user_id = g.user_id
INNER JOIN
(
    SELECT u.user_id, MAX(g.file_name) AS file_name
    FROM Gallery g
    LEFT JOIN Users u
        ON u.user_id = g.user_id
    WHERE g.status = 'approved'
    GROUP BY u.user_id
) t
    ON u.user_id   = t.user_id AND
       g.file_name = t.file_name
ORDER BY g.date_added DESC
LIMIT 10

well, the filenames were just an example. they are actually random words. I looked through the duplicate link as suggested above, but the fiddle link doesn't work. One of the answers doesn't make sense to me because it looks inside the users table, which I don't really need to look into it... just want to eliminate the "duplicated" users inside Gallery table. — Hugo, Dec 20 '16 at 19:12

How to show a filtered result ONCE in a MySql query

1 Answers1