SQL query to select all unique tuples

Question

I have the following data:

Now what I am trying to do is get all unique rows (sorted by created_at) where the source_account_id and target_account_id is unique and latest row for source or target == 1

I have tried this but it returns 4 rows when it should return basically only 2:

select
    *
from
    messages
where
    source_account_id = 1 OR target_account_id = 1
group by
    source_account_id,
    target_account_id

The result I expect is 2 rows with message_id = 3, 6.

To sum it up really I want the latest row (message) for account_id = 1 and any people he has sent or received a message from

Any ideas?

Possible duplicate of [How to select unique records by SQL](https://stackoverflow.com/questions/1641718/how-to-select-unique-records-by-sql) — Maifee Ul Asad, Jan 23 '19 at 19:28
no because in my case i dont get which way the source and target are — strangeQuirks, Jan 23 '19 at 19:31
no not quite, you see (source_account_id, target_account_id) [1,2] and [2,1] would be the same for me. but in that case i should return the row that is latest between 1,2. I basically want to get all rows for an account_id = 1 (send or received) and get the latest — strangeQuirks, Jan 23 '19 at 19:37
@user1009698 - can you provide the `CREATE TABLE` and `INSERT INTO` commands for the data. avoid posting data as screenshot. — Sebastian Brosch, Jan 23 '19 at 19:38
Are you running MySQL or sqlite ? You tagged both, but these are different RDBMS. — GMB, Jan 23 '19 at 19:41

score 2 · Accepted Answer · answered Jan 23 '19 at 19:40

I think you can use a correlated subquery for what you want::

select m.*
from messages m
where m.created_at = (select max(m2.created_at)
                      from messages m2
                      where (m2.source_account_id = m.source_account_id and
                             m2.target_account_id = m.target_account_id
                            ) or
                            (m2.source_account_id = m.target_account_id and
                             m2.target_account_id = m.source_account_id
                            )
                     ) and
      1 in (m.source_account_id, m.target_account_id);

SQL query to select all unique tuples

1 Answers1