Can two columns in one table be grouped together to extract only one of the most recent rows?

Column Sender | Message Code | Contents | Recipient | Time Sent

 A           ME-1       "AA"      B          12:30
 A           ME-1       "AA"      B          12:32      
 B           ME-1       "AA"      A          12:31
 B           ME-1       "AA"      A          12:33
 A           ME-1       "AA"      C          12:30
 A           ME-1       "AA"      C          12:32
 C           ME-1       "AA"      A          12:31
 C           ME-1       "AA"      A          12:33

There's a table like this

Don't enter B and C directly, just A The most recent line in the conversation between A and B, The most recent line in the conversation between A and C I want to take it out, is it possible?

oracle

2022-09-21 21:30

1 Answers

This answer is based on the following assumptions:

The answer to the question is as follows.

select speaker, max(timestamp)
from 
    (select (if(sender ='A') then receiver else sender) as speaker, timestamp
    from table where sender = 'A' or receiver = 'A') opponent
group by speaker

To illustrate,

select (if(sender ='A') then receiver else sender) as speaker, timestamp
from table where sender = 'A' or receiver = 'A'

Make a table opponent temporarily that binds the records of the call with A, and

You can group_by this opponent table with speaker to find the max value of timestamp.

To explain in a table

select (if(sender ='A') then receiver else sender) as speaker, timestamp
from table where sender = 'A' or receiver = 'A') opponent

2022-09-21 21:30

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656