Exclude rows having similar fields with another row(s)

December 30, 2021

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. for my case I want to get smth 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 field (like createdAt or updatedAt) — correct me and let me know if my question still is not clear, please.

P.S. Answers with pure SQL lang are accepted also.

>Solution :

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
);