combining two columns/ unique select?

pizzaengineer

Hey guys I need some help here filtering and grouping similar items in two columns. I have a message table in my database laid out as follows:

+--------+-----------+----------+-------------+
| id_msg | idusr_msg | user_msg | content_msg |
+--------+-----------+----------+-------------+
| 1 | 3 | 1 | hey |
| 2 | 3 | 1 | gggggggg |
| 3 | 3 | 2 | hey |
| 4 | 3 | 2 | fdfdsfds |
| 5 | 2 | 1 | hey |
| 6 | 1 | 2 | fff |
| 7 | 1 | 3 | ffff |
+--------+-----------+----------+-------------+

id_msg is the primary key, idusr_msg is the id of the user who wrote the message and user_msg is the destination of the message, and content_msg holds the actual message.

Here is my select statement:

SELECT max(id_msg), idusr_msg, user_msg, content_msg
FROM message_msg1
WHERE idusr_msg =1 OR user_msg = 1
GROUP BY user_msg, idusr_msg

and this results

+-------------+-----------+----------+-------------+
| max(id_msg) | idusr_msg | user_msg | content_msg |
+-------------+-----------+----------+-------------+
| 5 | 2 | 1 | hey |
| 2 | 3 | 1 | hey |
| 6 | 1 | 2 | fff |
| 7 | 1 | 3 | ffff |
+-------------+-----------+----------+-------------+

I want to be able to pull the most recent message (using the max) that correspond with user #1. I need the select statement to see that record #5 and #6 are similar and only return the most recent record (same with #2 and #7 but only return #7).

Ultimately I'd like to have it return the data as below:

+-------------+-----------+----------+-------------+
| max(id_msg) | idusr_msg | user_msg | content_msg |
+-------------+-----------+----------+-------------+
| 6 | 1 | 2 | fff |
| 7 | 1 | 3 | ffff |
+-------------+-----------+----------+-------------+

Any ideas on how to do this? Thanks

tomhath

It would be something like this. I didn't test the query, but you need to find the id_msg you want for each idusr_msg, then join back to the same table to get everything for that row.

The examples you give don't make sense to me.

SELECT m1.id_msg, m1.idusr_msg, m1.user_msg, m1.content_msg
FROM message_msg1 m1
INNER JOIN (
  SELECT max(id_msg) as id_msg, idusr_msg 
  FROM message_msg1
  GROUP BY idusr_msg 
  ) as m2
ON m2.id_msg = m1.id_msg 
WHERE m1.idusr_msg =1