Select distinct + select top to merge multiple rows

Question

I'm trying to select rows from a table, one row per email address, and return one firstname from the top row in the email list. The query, though, returns multiple email addresses. What am I doing wrong?

SELECT 
    DISTINCT email,
    (SELECT TOP 1 firstname 
     FROM onsite_clients_archive oc 
     WHERE oc.client_id=oca.client_id 
     ORDER BY client_id)
FROM onsite_clients_archive oca 
WHERE users_user_id IS NULL

Answer 1

Your bug is WHERE oc.client_id = oca.client_id should be WHERE oc.email = oca.email.

You didn't say which DBMS you're using, but if it's MS SQL, the following will also do what you want.

SELECT email, firstname
FROM (
    SELECT 
        email, firstname, 
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY client_id DESC) AS intRow
    FROM onsite_clients_archive
    WHERE users_user_id IS NULL
) AS T
WHERE intRow = 1

Answer 2

The DISTINCT will apply to all columns in your column list, so in this case if there are multiple rows in the Onsite_Clients_Archive with the same email address then you will get those back from the query as long as they have a different name.

Think about what you're trying to do... you want each distinct (not meaning the SQL keyword necessarily here) email address with the first name that matches for it (first defined by order of client_id). This is difficult for me to say definitively without seeing the actual data model, but here's my first guess:

SELECT
     T1.email,
     T1.first_name
FROM
     Onsite_Clients_Archive T1
LEFT OUTER JOIN Onsite_Clients_Archive T2 ON
     T2.email = T1.email AND
     T2.client_id < T1.client_id
WHERE
     T2.client_id IS NULL   -- Assuming that this is a not null column, the only way it could be NULL is if there was no match