SQL Conditional join based on Rank

Question

I am trying to join two tables based on Instrument and Rank. if Instrument and Rank matches then it is a direct join. Else join should seek the next available rank in order. for example if Instrument CDF and Rank 1 is not matching in Table 2 then sql query should seek CDF and Rank 2 to match. If it matches then move to next Instrument and Rank in table 1.

Output should have as many row as in Table 1 and only matched qty from table 2 based on logic above. Table 1 will not have duplicates.

Any algorithm or sample code will be a great help.

Test:

Output should have 3 rows as per sample data.

ABC 1 from Table 1 should match with ABC 1 from Table 2 and return qty 55.
CDF 1 from Table 1 should be matched to CDF 2 from Table 2 and return qty as 56.
CDE 2 from Table 1 should match to CDE 4 (not rank 1) from table 2 and return qty as 91.

Hope this will clarify the request.

Expected results from example data:

Instrument	Qty
ABC	55
CDF	56
CDE	91

Example Data

Table 1:

Instrument	Rank	Id	position
ABC	1	2	A1
CDF	1	78	Abg
CDE	2	65	dfv

Table 2:

Ins	Qty	Rank
ABC	55	1
ABC	65	2
ABC	76	4
CDF	56	2
CDF	55	3
CDF	33	4
CDE	78	1
CDE	91	4

Does this answer your question? [SQL join on multiple columns in same tables](https://stackoverflow.com/questions/16597660/sql-join-on-multiple-columns-in-same-tables) — Mikael, Jun 05 '21 at 16:50
I removed the conflicting DBMS tags. Please add only the tag for the database product you are really using. — , Jun 05 '21 at 19:18

score 0 · Answer 1 · answered Jun 05 '21 at 18:09

select
  *,
  (
    select qty
    from table2 as t2
    where t2.instrument = t1.instrument and t2.rank >= t1.rank
    order by t2.rank limit 1
  ) as qty
from table1 as t1;

If you need more then one columns from the table2 then use cross join lateral instead

SQL Conditional join based on Rank

1 Answers1