Oracle SQL - select pairs where an index value has its smallest value, EXCEPT when there has already-CodePudding

I am trying to select rows from a table where an index column has its minimum value within a partition, under the condition that this row has not already had a match in a previous partition. The selection should work in a kind of draw without replacing-manner.

Maybe it is better to clarify with an example. Consider the following table:

ID1   ID2   index
a1    b1    1
a1    b2    2
a1    b3    3
a2    b1    4
a2    b2    5
a2    b3    6

For the group in ID1, I want to pick the match from ID2 with the minimum index value for this partition (here: b1 with an in index value of 1). Now, for ID1=a2, the matching ID2 would be b2 (with an index value of 4), since, b1 has already been picked (and matched to a1).

So the resulting query result should be

ID1   ID2   index
a1    b1    1
a2    b2    5

Note that not all values in ID1 have all possible values in ID2 and vice versa, also the subgroup sizes in ID1 and ID2 are not constant.

Any ideas how I could achieve this?

CodePudding user response：

you can try this.

with table_1 
as
(
          Select 'a1' as ID1, 'b1' as ID2, 1 as "index" from dual
Union all Select 'a1' as ID1, 'b2' as ID2, 2 as "index" from dual
Union all Select 'a1' as ID1, 'b3' as ID2, 3 as "index" from dual
Union all Select 'a2' as ID1, 'b1' as ID2, 4 as "index" from dual
Union all Select 'a2' as ID1, 'b2' as ID2, 5 as "index" from dual
Union all Select 'a2' as ID1, 'b3' as ID2, 6 as "index" from dual
Union all Select 'a2' as ID1, 'b2' as ID2, 8 as "index" from dual
)
Select 
    ID1,
    ID2,
    "index"
from
(
Select 
ID1,
ID2,
"index",
rank() over (PARTITION BY ID1 order by "index") rank
from 
    table_1
where
    substr(id1,2,1) = substr(id2,2,1)
)x
where rank = 1

CodePudding user response：

Not 100% sure but looks like the solution is where

DENSE_RANK() OVER(ORDER BY id1) AS rn1
, DENSE_RANK() OVER(ORDER BY id2) AS rn2

are equals.