Select DISTINCT rows from multiple columns in mysql
I have a table called sk_messages
. The structure looks like this:
msg_id msg_from_user_id msg_to_user_id msg_text msg_date msg_status
1 12 14 hai... 23-12-2013 unread
2 12 14 .... ... unread
3 13 14 ... .. unread
My requirement is that I want to display all messages that are for the current user, with the proviso that one message should be displayed from the sender, even if he sends multiple messages with an unread status. From the above context, one post from user ID 12. I tried the following query but it doesn't work.
SELECT DISTINCT (msg_from_user_id), msg_text, msg_date
FROM sk_messages
WHERE msg_to_user_id = '$user_id'
AND msg_status = 'unread'
ORDER BY msg_date
$user_id is the id of the login user
+3
source to share
3 answers
Try group
user by id.
SELECT msg_text, msg_date
FROM sk_messages
WHERE msg_to_user_id = '$user_id'
AND msg_status = 'unread'
GROUP BY msg_from_user_id
ORDER BY msg_date
Tested code for getting last message
'SELECT * FROM ( SELECT * FROM message WHERE user_id = 1 ORDER BY created DESC LIMIT 1) as msg GROUP BY user_id '
+6
source to share
Try this instead:
SELECT
m1.msg_from_user_id,
m1.msg_text,
m1.msg_date
FROM sk_messages AS m1
INNER JOIN
(
SELECT msg_from_user_id, MAX(msg_date) AS LatestDate
FROM sk_messages
WHERE msg_to_user_id = '$user_id'
AND msg_status = 'unread'
GROUP BY msg_from_user_id
) AS m2 ON m1.msg_from_user_id = m2.msg_from_user_id
AND m1.msg_date = m2.LatestDate
ORDER BY m1.msg_date;
SQL Fiddle Demo
+3
source to share