CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Lee Loftiss
Lee Loftiss

Reputation: 3205

Extract only 1 message from all senders

I am building a messaging system and need to extract only the last message from each sender to a specified recipient. So, if 3 people each sent 5 messages(total of15 messages) to the recipient, I need to get 3 entries; the last message from each sender.

Here is my current SQL:

SELECT  
                        messages.*, 
                        user_accounts.uacc_id,
                        user_accounts.uacc_username,
                        user_profiles.upro_image_name
                    FROM messages
                        LEFT JOIN user_accounts 
                            ON messages.msg_from_uacc_fk = user_accounts.uacc_id
                        LEFT JOIN user_profiles 
                            ON user_profiles.upro_uacc_fk = user_accounts.uacc_id
                    WHERE 
                        messages.msg_to_uacc_fk = ?
                    ORDER BY 
                        msg_id
                    DESC

I tried adding 'MAX(1)' to the SELECT as well as 'LIMIT = 1' to after the DESC, but, of course, this just returned a total of 1 message.

Upvotes: 0

Views: 52

Answers (2)

John Woo
John Woo

Reputation: 263803

It is sometimes hard to guess how the tables are designed but this query below uses a subquery to get the latest message for each user.

SELECT  a.*, 
        c.uacc_id,
        c.uacc_username,
        d.upro_image_name
FROM    messages a
        INNER JOIN
        (
            SELECT  msg_from_uacc_fk, MAX(msg_id) max_id
            FROM    messages 
            GROUP   BY msg_from_uacc_fk
        ) b ON  a.msg_from_uacc_fk = b.msg_from_uacc_fk AND
                a.msg_id = b.max_id
        INNER JOIN user_accounts c
            ON  a.msg_from_uacc_fk = c.uacc_id
        INNER JOIN user_profiles d
            ON d.upro_uacc_fk = c.uacc_id
WHERE   a.msg_to_uacc_fk = ?

If this doesn't solve the problem, please add sample records along with your question :)

Upvotes: 2

John Bingham
John Bingham

Reputation: 2006

Can you not simply use a group by? 

SELECT u.uacc_username, max(m.msg_id) as LatestMsg
FROM messages m JOIN user_accounts u on m.msg_from_uacc_fk = u.uacc_id
WHERE m.msg_to_uacc_fk = ?
GROUP BY u.uacc_username

Upvotes: 1

Related Questions