ORDER BY Mysql - Reporting organisation

Question

I have a chat and would like to display the contacts according to the date of the last message, I thought of many ways to do this but it is not working in any way. How could I bring this data properly?

Structure of the Tables

The table message stores all messages, however, each message has the session_id.

The table Session has session information, ie messages are organized by session, within Session, has the contact ID.

The table contact has all contact information.

Goal

My idea is to give the SELECT in the contact table, but arrange them by the time of the last message.

For example, imagine a chat with your next-door contacts, you’re exchanging messages with some of them, but even if you’re exchanging messages, this contact is being displayed below some that you talked to yesterday.

The idea is this contact you’re talking to right now, climb to the top of your contacts.

Example

Below is an example of one of SELECTS that went wrong, it even returns in order, but with each message, the contacts are repeated.

SELECT * 
  FROM      contact AS c
 INNER JOIN message AS m
 INNER JOIN session AS s ON s.contact_id = c.id AND m.session_id = s.id
 ORDER BY m.timestamp;

The column of the time at which the message was sent is timestamp

If anyone can help, I’d appreciate it!

OBS: My forte is not database.

Answer 1

From the version 8 of MySQL you can use the function ROW_NUMBER as follows:

Schema (Mysql V8.0)

CREATE TABLE contact (
  id      INTEGER PRIMARY KEY AUTO_INCREMENT,
  contact VARCHAR(100)
);

INSERT INTO contact(contact)
             VALUES('Maria'),
                   ('José'),
                   ('João');

CREATE TABLE session (
  id         INTEGER PRIMARY KEY AUTO_INCREMENT,
  contact_id INTEGER
);

INSERT INTO session(contact_id)
             VALUES(1),
                   (2),
                   (3);

CREATE TABLE message (
  id         INTEGER PRIMARY KEY AUTO_INCREMENT,
  session_id INTEGER,
  message    VARCHAR(100),
  timestamp  TIMESTAMP
);

INSERT INTO message(session_id, message, timestamp)
             VALUES(1, 'Oi', NOW() - INTERVAL 2 MINUTE), -- Conversa da Maria
                   (3, 'Como foi?', NOW() - INTERVAL 30 SECOND), -- Conversa do João
                   (1, 'Tudo bem?', NOW() - INTERVAL 15 SECOND); -- Conversa da Maria

---

Query

SELECT x.contact,
       x.message,
       x.timestamp
  FROM (SELECT c.contact,
               m.message,
               m.timestamp,
               ROW_NUMBER() OVER(PARTITION BY c.id ORDER BY m.timestamp DESC) AS sequencia
          FROM contact c
         INNER JOIN session s ON s.contact_id = c.id
         INNER JOIN message m ON m.session_id = s.id
       ) x
  WHERE x.sequencia = 1
  ORDER BY x.timestamp DESC;

---

Resulting in

| contact | message   | timestamp           |
| ------- | --------- | ------------------- |
| Maria   | Tudo bem? | 2019-04-09 18:58:16 |
| João    | Como foi? | 2019-04-09 18:58:01 |

---

See working on DB Fiddle

---

ROW_NUMBER

Returns the number of the Current Row Within its Partition.

In free translation:

Returns the number of the current row inside your partition.

---

For versions previous to the MySQL (tested from the version 5.5):

Query

SELECT z.contact,
       z.message,
       z.timestamp
FROM (
  SELECT x.contact,
         x.message,
         x.timestamp,
         @sequencia := CASE x.id WHEN @anterior THEN @sequencia + 1 ELSE 1 END AS sequencia,
         @anterior := x.id
    FROM (SELECT c.contact,
                 m.message,
                 m.timestamp,
                 c.id
            FROM contact c
           INNER JOIN session s ON s.contact_id = c.id
           INNER JOIN message m ON m.session_id = s.id
           JOIN (SELECT @anterior := 0, @sequencia := 1) AS y
           ORDER BY c.id,
                    m.timestamp DESC
         ) x
) z
  WHERE z.sequencia = 1
  ORDER BY z.timestamp DESC;

---

Resulting in

| contact | message   | timestamp           |
| ------- | --------- | ------------------- |
| Maria   | Tudo bem? | 2019-04-10 12:19:28 |
| João    | Como foi? | 2019-04-10 12:19:13 |

---

See working on DB Fiddle.

---

Answer 2

See if this SQL helps you with anything:

select distinct 
       c.campo1
      ,c.campo2
      ,c.campo3
      ,c.campo4
  from      message m 
 inner join session s on s.id = m.session_id
 inner join contact c on c.id = s.contact_id
 order by m.timestamp;

In the case of SQL above, only the data of clients who have at least one message sent will be returned. As you will only need the contact information, I believe using distinct already solve the problem of bringing duplicate data.