'How to use Distinct in SQL

Using SQL I have

| Message_From | Message_To  |Message| Message_Time(Current TimeStamp)|
| -------------| ------------|------ | -------------------------------|
| [email protected]|[email protected]| Abc   |2022-04-22 12:26:13             |
| [email protected]|[email protected]| DEF   |2022-04-22 12:26:31             |
| [email protected]|[email protected]| CED   |2022-04-22 12:26:51             |

I want to be show as

| Message_From | Message_To  | Message_Time(Current TimeStamp)|
| -------------| ------------|-------------------             |
| [email protected]|[email protected]|2022-04-22 12:26:13             |
| [email protected]|[email protected]|2022-04-22 12:26:51             |

I want to select the distinct Message_To With the the last Message sent.

I have written a query but its not working as my query is showing all the 3 peoples because the Message time stamp is different

SELECT DISTINCT 
    [Message_From], [Message_To] 
FROM 
    [messages] 
WHERE 
    [Message_From] = '$Email'

Any idea for this?

mysqlsql


Solution 1:[1]

On MySQL 8+, use ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Message_From, Message_To
                                 ORDER BY Message_Time DESC) rn
    FROM messages
)

SELECT Message_From, Message_To, Message, Message_Tim
FROM cte
WHERE rn = 1;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions