Exclude rows having similar columns with another row(s)

Question

Let's suggest I have the following table of rates:

  ID   |   baseAsset  |    quoteAsset  
-------+--------------+-----------------
   1   |     BTC      |      USDT         
   2   |     USDT     |      BTC         
   3   |     LUNA     |      ETH          
   4   |     ETH      |      LUNA         
   5   |     USD      |      BTC          

The problem is that I have no idea how to get rates and exclude those rates that have reverse pair. E.g. in my case I want to get something like this:

  ID   |   baseAsset  |    quoteAsset  
-------+--------------+-----------------
   1   |     BTC      |      USDT        
   3   |     LUNA     |      ETH          
   5   |     USD      |      BTC          

Now there is only BTC/USDT (without USDT/BTC). So, the query should take only first pair (no matter BTC/USDT, or LUNA/ETH, or another one) and find reverse pair to exclude. If there should be additional column (like createdAt or updatedAt) — correct me and let me know if my question still is not clear, please.

Answer 1

In SQL you'd use LEAST and GREATEST for this, provided your DBMS supports these functions:

select
  min(id),
  least(baseAsset, quoteAsset) as asset1,
  greatest(baseAsset, quoteAsset) as asset2,
from mytable
group by least(baseAsset, quoteAsset), greatest(baseAsset, quoteAsset)
order by min(id);

In standard SQL you can replace above LEAST by

CASE WHEN baseAsset < quoteAsset THEN baseAsset ELSE quoteAsset END

and above GREATEST by

CASE WHEN baseAsset > quoteAsset THEN baseAsset ELSE quoteAsset END

Another simple approach is to use NOT EXISTS:

select *
from mytable
where not exists
(
  select null
  from mytable reverse
  where reverse.baseAsset = mytable.quoteAsset
  and reverse.quoteAsset = mytable.baseAsset
  and reverse.id < mytable.id
);