MySQL doubt on Select

Question

MySQL doubt on Select

Navigation

#1 by (2 votes)

1

I have a table of chats in mysql , in it is 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 via select , to return the last conversation that occurred between people.

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

How could I do this via MySQL ?

The result would look like this:

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

mysql sql

asked by anonymous 06.11.2016 / 13:47

1 answer

Implementation of the strcpy function of C in MIPS using the MARS simulator ListView does not appear in the emulator

score 2 · Answer 1

Considering that the name of your table would be chat to query would look more or less similar to 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 account that the id is sequential.