Simple query

primary_id / from_user_id / to_user_id / time / message

1 / 33 / 189 / 1334865983 / “Hello there”
2 / 44 / 76 / 1334897600 / “Hi Dave”
3 / 63 / 33 / 1334907887 / “Hello, message here”
4 / 33 / 63 / 1334908903 / “Hi again”

I have a simple table like above for storing messages sent between 2 users.

I need to display one record for each ‘conversation’ that a user has, so in this example, user 33 has had 2 conversations:

  • conversation 1: one message sent by user 33 in record 1 to user 189 (no reply messages or other messages between the two users)
  • conversation 2: one message received by 33 in record 3 and one sent in record 4, both to user 63

I want to display the most recent message and show it like a header page where they click and view all messages between the two users.

I only need help on the query to retrieve the data from table above so I can display like below. I have everything else under control.

I am thinking GROUP BY, but how do I return the latest message (and time field) and how do I GROUP BY considering the user id could be in either the from_user_id or to_user_id field?

So I need to show the data like this in a header page (I just need help on the query that will sort the data to allow me to do this):

User 33’s messages:

Conversation number 1 (with user 189)
(date = 1334865983) Hello there (shows latest message)

Click HERE to View

Conversation number 2 (with user 63)
(date = 1334908903) Hi again (shows latest message)

Click HERE to View

What database are you using?

this is untested

SELECT m.with_user
     , m.time
     , m.message
  FROM ( SELECT w.with_user
              , MAX(w.time) AS max_time
           FROM ( SELECT CASE WHEN t.from_user_id = 33
                              THEN t.to_user_id
                              ELSE t.from_user_id
                          END AS with_user
                       , t.time
                    FROM messages AS t
                   WHERE [COLOR="#FF0000"]33 [/COLOR]IN ( t.from_user_id , t.to_user_id ) ) AS w
             BY w.with_user ) AS x
  JOIN messages AS m
    ON m.time = x.max_time
   AND ( m.from_user_id = [COLOR="#FF0000"]33 [/COLOR]and m.to_user_id = x.with_user
      OR m.from_user_id = x.with_user and m.to_user_id = [COLOR="#FF0000"]33 [/COLOR])

If you are using SQL Server (2005 onwards) or Oracle you can look at the RANK or DENSE_RANK function partitioned by user ID and ordered by message datetime descending.

The approach would be a selecting from a derrived table including a column of RANK where the rank column equals 1.

I don’t think MySQL supports a RANK function, which is unfortunate as you will probably have to go with the much less efficient join on I’d with a MAX function.

Sent from my BlackBerry 9900 using Tapatalk

Thanks r937, but it has errors.

It keeps saying w.with_user isn;t a valid field. I then messed around and it started saying that m.with_user isn’t a valid field.

Any ideas?

please show your query, and the error message

Thanks for the reply. This is what is displayed:

Unknown column ‘m.with_user’ in ‘field list’
SELECT m.with_user , m.time , m.msg FROM ( SELECT w.with_user , MAX(w.time) AS max_time FROM ( SELECT CASE WHEN t.from_id = 22 THEN t.to_id ELSE t.from_id END AS with_user , t.time FROM messages_table AS t WHERE 22 IN ( t.from_id , t.to_id ) ) AS w GROUP BY w.with_user ) AS x INNER JOIN messages_table AS m ON m.time = x.max_time AND ( m.from_id = 22 and m.to_id = x.with_user OR m.from_id = x.with_user and m.to_id = 22 )

‘with_user’ isn’t a field in my table, but I’m pretty sure that it isn’t supposed to be based on the query you provided. The exact fields in my table are:

  • time
  • msg
  • to_id
  • from_id

Try x.with_user ?

No that doesn’t work either.

Thanks for looking though.

Sorry, my mistake, changing to ‘x.with_user’ works perfectly.

I had been messing around with other parts of the query and when I realised my error it worked straight away.

Thanks again to both.

well done

problem is, this wasn’t your error, it was mine!!! :blush: