CODEWITHSUNDEEP
phpmysql
Giuseppe Lodi Rizzini
Giuseppe Lodi Rizzini

Reputation: 1055

SELECT WHERE LAST ROW HAS A CONDITION TRUE IN A FIELD

I need to get only CHAT IDs where LAST message (for each chat) came from "user" and ignore chat where last message came from "support" but I don't understand the logic:

Table CHAT

ID  |  DateStart
-----------------
 1  |  2020-01-01
-----------------
 2  |  2020-02-01
-----------------
 3  |  2020-03-01

Table MESSAGES

ID  | CHATID  | FROM_U   | MESSAGE  |  DATE    |
---------------------------------------------
 1  |    1    | user   | hi...    | 2020-01-01 10:00:00
---------------------------------------------
 2  |    1    | support| response | 2020-01-01 12:00:00
---------------------------------------------
 3  |    1    | user   | response2| 2020-01-01 12:10:00
---------------------------------------------
 4  |    2    | support| hi....   | 2020-02-01 09:00:00
---------------------------------------------
 5  |    2    | user   | test...  | 2020-02-01 09:10:00
---------------------------------------------
 6  |    2    | support| test...  | 2020-02-01 09:20:00
---------------------------------------------
 7  |    3    | support| hi....   | 2020-03-01 09:00:00
---------------------------------------------
 8  |    3    | user   | test...  | 2020-03-01 09:10:00

So in my example, i need to retrieve only chat IDs: 1 and 3 cause last message (based on DATE) is written by an "user"

I need to do this in php so I can split in multiple queries but i'd like to know if it possible to do that in a simple unique query.

I was thinking for a query like that:

SELECT CHATID, FROM_U, MAX(DATE) FROM MESSAGES GROUP BY CHATID HAVING FROM_U = 'user'

but it doesn't work as expected

Upvotes: 2

Views: 45

Answers (1)

Rahul Biswas
Rahul Biswas

Reputation: 3467

Please try this.

-- MySQL(v5.8)
SELECT t.CHATID
FROM (SELECT CHATID
     , FROM_U
     , DATE
     , ROW_NUMBER() OVER (PARTITION BY CHATID ORDER BY DATE DESC) row_num
FROM MESSAGES ) t
WHERE t.row_num = 1
    AND t.FROM_U = 'user';

Alternate query

-- MySQL(v5.7)
SELECT m.CHATID
FROM MESSAGES m
INNER JOIN (SELECT CHATID
                 , MAX(DATE) max_date
            FROM MESSAGES
            GROUP BY CHATID) t
        ON m.CHATID = t.CHATID
       AND m.DATE = t.max_date
       AND m.FROM_U = 'user';

Upvotes: 3

Related Questions