How to use Distinct in SQL

Question

Using SQL I have

| Message_From | Message_To  |Message| Message_Time(Current TimeStamp)|
| -------------| ------------|------ | -------------------------------|
| abc@gmail.com|pof@gmail.com| Abc   |2022-04-22 12:26:13             |
| abc@gmail.com|gof@gmail.com| DEF   |2022-04-22 12:26:31             |
| abc@gmail.com|gof@gmail.com| CED   |2022-04-22 12:26:51             |

I want to be show as

| Message_From | Message_To  | Message_Time(Current TimeStamp)|
| -------------| ------------|-------------------             |
| abc@gmail.com|pof@gmail.com|2022-04-22 12:26:13             |
| abc@gmail.com|gof@gmail.com|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?

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Apr 22 '22 at 08:23
It looks to me like the OP intends to get the latest line for a given `message_to` column value. — blabla_bingo, Apr 22 '22 at 08:26
Please do not vandalise your question. I’ve locked the post for a while, to make it explicit that we take content integrity serious. — Martijn Pieters, Apr 22 '22 at 09:58

Tim Biegeleisen · Accepted Answer · 2022-04-22T08:38:26.943

2

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;

edited Apr 22 '22 at 08:38

answered Apr 22 '22 at 08:25

Tim Biegeleisen

502,043
27
286
360

This may go a bit extreme , but If by chance for a given Message_To value we have two rows with identical message_time, how can we determine the latest ? Would it be decided by MySQL 's INSERT SEQUENCE ? If so, should we add an auto increment ID column to mark the sequence of INSERT? – blabla_bingo Apr 22 '22 at 08:35
@blabla_bingo In that case, you would need to add another sorting level to the `ORDER BY` clause in `ROW_NUMBER` to break the tie. – Tim Biegeleisen Apr 22 '22 at 08:38
Do you think it's worth adding an AI column and an addtional layer in the ORDER BY clause for the sake or high precision? Personally, I think it's too redundant to do so : ) – blabla_bingo Apr 22 '22 at 08:49

How to use Distinct in SQL

1 Answers1