unique field from mysql database to php

Question

I have a sample database like this, in which, id is always unique, but the user_id is not unique.

id,user_id,message,msg_datetime
111,u1, msg from u1,time present here
112,u2, msg from u2,time present here
113,u3, msg from u3,time present here
114,u2, msg from u2,time present here
115,u7, msg from u7,time present here
116,u2, msg from u2,time present here
117,u1, msg from u1,time present here
118,u5, msg from u5,time present here

so i want to grab only those unique users who have messaged and order them in DESC by msg_datetime.

This is the query i have.
select id,DISTINCT user_id,msg_datetime ORDER BY msg_datetime DESC but i am getting an error as:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT. any help here? what is the correct syntax for what i am trying to achieve?

I want to show only one entry for each user, it does not matter which ID i am showing but only 1 per user.

Answer 1

SELECT syntax:

SELECT (fieldlists)
FROM (table)
[WHERE (conditions)]
ORDER BY (something)

You kindof forgot to say which table you want the data from.

Answer 2

With the assumption your table name is TABLE (since you do not specify)

SELECT t1.id,t1.user_id,t1.msg_datetime
FROM TABLE t1
WHERE id=(SELECT id FROM TABLE 
          WHERE user_id=t1.id 
          ORDER BY msg_datetime DESC 
          LIMIT 1)
ORDER BY t1.msg_datetime DESC

Answer 3

If you don't care which record with the same user_id query should return, then

SELECT id,user_id,msg_datetime FROM table_1 GROUP BY user_id ORDER BY msg_datetime DESC

If you want to display, for instance, the last record for each user, you need

SELECT a.user_id, a.last_time, b.id 
FROM
(SELECT user_id, MAX(msg_datetime) as last_time 
 FROM table1)a
INNER JOIN table1 b ON (b.user_id = a.user_id AND b.msg_datetime = a.last_time)

ORDER BY a.last_time;

Answer 4

You misunderstand how DISTINCT works. It works on rows not fields. What you want is a groupwise maximum, also known as greatest-n-per-group.

Here's one way to do it in MySQL:

SELECT id, user_id, message, msg_datetime
FROM (
    SELECT
        id, user_id, message, msg_datetime,
        @rn := CASE WHEN @prev_user_id = user_id
                    THEN @rn + 1
                    ELSE 1
               END AS rn,
        @prev_user_id := user_id
    FROM (SELECT @prev_user_id := NULL) vars, Table1 T1
    ORDER BY user_id, msg_datetime DESC
) T2
WHERE rn = 1
ORDER BY msg_datetime