CODEWITHSUNDEEP
mysql
Tomislav Tomi Nikolic
Tomislav Tomi Nikolic

Reputation: 638

Selecting unique values from SQL

Alright so I am trying to select only unique values from MySQL but I aint succeeding with it.

Basically I am trying to get LAST messages user received from all users

Query

SELECT DISTINCT private_messages.*,
                    users.ID AS userID,
                    users.username AS username,
                    profiles.img_url AS profileImage
                    FROM private_messages
                    INNER JOIN users ON users.username = private_messages.receiver
                    INNER JOIN profiles ON profiles.userID = users.ID
                WHERE private_messages.sender ='Admin' 
                ORDER BY private_messages.sent_at DESC

Now the problem is, I want to select only last messages from receivers, not all of them

Current result

enter image description here

What I want to achieve?

Upvotes: 1

Views: 59

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42853

If I correctly understand, if you have user bla as receiver user, you need get all newest messages sent for him from all users.

If so, try this

SELECT private_messages.*,
            users.ID AS userID,
            users.username AS username,
            profiles.img_url AS profileImage 
FROM private_messages INNER JOIN (
    SELECT MAX(id) as maxid from private_messages 
    where receiver = 'bla'
    group by sender
) m
ON private_messages.id = m.maxid
INNER JOIN users ON users.username = private_messages.sender 
INNER JOIN profiles ON profiles.userID = users.ID
ORDER BY private_messages.sent_at DESC

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

You shoudd filter for max (id)

SELECT DISTINCT private_messages.*,
                users.ID AS userID,
                users.username AS username,
                profiles.img_url AS profileImage
                FROM private_messages
                INNER JOIN users ON users.username = private_messages.receiver
                INNER JOIN profiles ON profiles.userID = users.ID
            WHERE private_messages.sender ='Admin' 
            AND  private_messages.id in  ( select max(id) 
                  from private_messages group by sender 
   )                
  ORDER BY private_messages.sent_at DESC

Upvotes: 1

Related Questions