Kashan Shah Kashan Shah - 4 years ago 114
SQL Question

How could I select a distinct row from the following table

I want to select the maximum ID from a combination of SentBy and SentTo
I mean I want To Group (SentTo-SentBy) like 1-3 and 3-1 are grouped together.. Similarlt 1-2 and 2-1 should also be grouped together

What will be the query?

Users:

**ID | Name**

1 | A

2 | B

3 | C

4 | D


Chat

ID | Msg | SentBy (Users.ID) | SentTo (Users.ID)

1 | abc | 1 | 2

2 | def | 1 | 3

3 | ghi | 1 | 4

4 | jkl | 2 | 1

5 | mno| 2 | 1

6 | pqr | 3 | 1

7 | stu | 3 | 1

8 | vwx| 4 | 1

9 | yz | 4 | 1

10| def | 1 | 3

12| jkl | 2 | 1


Required

ID | Msg | SentBy (Users.ID) | SentTo (Users.ID)

3 | ghi | 1 | 4

10| def | 1 | 3

12| jkl | 2 | 1

Answer Source

I think you are looking for this

select max(id) from chat as c1 
inner join chat as c2 on (c1.sent_by = c2.sent_to and c1.sent_to = c2_sent_by) 
group by c1.sent_by, c1.sent_to ;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download