Removing duplicates from a SQL query (not just "use distinct")

Question

It's probably simple, here is my query:

SELECT DISTINCT U.NAME, P.PIC_ID
FROM USERS U, PICTURES P, POSTINGS P1
WHERE U.EMAIL_ID = P1.EMAIL_ID AND P1.PIC_ID = P.PIC_ID AND P.CAPTION LIKE '%car%';

but this will only remove duplicates where a row has both the same u.name and p.pic_id. I want it so if there is any duplicates of the names, it just leaves out the other rows. It's a weird query, but in general, how can I apply the distinct to a single column of the SELECT clause?

Answer 1

Arbitrarily choosing to keep the minimum PIC_ID. Also, avoid using the implicit join syntax.

SELECT U.NAME, MIN(P.PIC_ID)
    FROM USERS U
        INNER JOIN POSTINGS P1
            ON U.EMAIL_ID = P1.EMAIL_ID
        INNER JOIN PICTURES P
            ON P1.PIC_ID = P.PIC_ID
    WHERE P.CAPTION LIKE '%car%'
    GROUP BY U.NAME;