Mysql doubt in Select

Asked

Viewed 60 times

1

I have a table of chats on mysql, the record of conversations between users.

The fields are.

id, id_from, id_to, message

Example (My Data in DB):

1, 50, 10, 'Hello'
2, 10, 50, 'Nice?'
3, 50, 10, 'Yeah, And you?'
4, 10, 50, 'Me too'
5, 1, 20, 'Hello'
6, 20, 1, 'Nice?'
7, 1, 20, 'Yeah, And you?'
8, 20, 1, 'Me too'
8, 50, 1, 'Hey.....'
9, 1, 50, 'Whats???'
10, 50, 1, ': )'
11, 1, 50, 'LOL'

[user]
id
name

id 50 = Paul
id 1= Samuel
id 20= Donald
id 10 = Max

What I need is to see select, return to the last conversation that took place between people.

For example, in my select, i want to know the last two conversations (showing only the last message of each conversation) that the id 50 had Paul.

How could I do that MySQL?

The result would be like this:

4, 10, 50, 'Me too'
11, 1, 50, 'LOL'
mysql sql

  • Was the answer helpful? Don’t forget to choose one and mark it so it can be used if someone has a similar question!

1 answer

2

Taking into account that the name of your table would be chat to query would look more or less like the following:

SELECT c2.id,
       c2.id_from,
       c2.id_to,
       c2.message
  FROM (SELECT MAX(c1.id) as id
          FROM chats c1
         WHERE c1.id_from = 50 -- Paul
         GROUP BY c1.id_from, c1.id_to) ids
  INNER JOIN chats c2 ON c2.id = ids.id;

The subquery will group the ids grouped by source and destination user code. I also took into consideration that id is sequential.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.