Select DISTINCT rows of multiple columns in mysql

Question

I have a table called sk_messages.It's structure is like the following:

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 which are for the current user with a condition that single message should be displayed from a sender even if he sends multiple messages with the status unread.That is,from the above context, single message of the user having ID 12, should be displayed.I have tried the following query,but it doesnt 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

Foreach msg_from_user_id which message you want to get? the latest the first? — Mahmoud Gamal
– Mahmoud Gamal, Commented Feb 13, 2013 at 9:14

Dipesh Parmar · Accepted Answer · 2013-02-13 09:57:55Z

6

Try to 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 to get latest message

'SELECT * FROM ( SELECT * FROM message WHERE user_id = 1 ORDER BY created DESC LIMIT 1) as msg  GROUP BY user_id '

edited Feb 13, 2013 at 9:57

answered Feb 13, 2013 at 9:16

Dipesh Parmar

27.3k8 gold badges65 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Techy Over a year ago

@Dinesh Parmer I have tried to accept your answer,but it says i should wait for some more minutes.now it says about 4 minutes.After that i will accept your answer.I have already upvoted your answer

Techy Over a year ago

@Dinesh Parmar Oh sure.I have a little problem more.I am not getting the latest message from that particular user..

Dipesh Parmar Over a year ago

use ORDER BY msg_date DESC

Techy Over a year ago

let us continue this discussion in chat

Mahmoud Gamal · Accepted Answer · 2013-02-13 09:21:35Z

3

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

edited Feb 13, 2013 at 9:21

answered Feb 13, 2013 at 9:16

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Comments

Abhishek Bhandari · Accepted Answer · 2013-02-13 10:02:23Z

0

Use this

SELECT 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  GROUP BY msg_from_user_id

edited Feb 13, 2013 at 10:02

Abhishek Bhandari

6231 gold badge5 silver badges16 bronze badges

answered Feb 13, 2013 at 9:18

Raul

6071 gold badge6 silver badges17 bronze badges

Collectives™ on Stack Overflow

Select DISTINCT rows of multiple columns in mysql

3 Answers 3

4 Comments

SQL Fiddle Demo

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Related