Exclude rows having similar columns with another row(s)-CodePudding

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 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.

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

CodePudding user response：

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