Oracle SQL - select pairs where an index value has its smallest value, EXCEPT when there has already been a previous pair with a smaller value

petwri 0
Viewed 57

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
foo   qux    1
foo   quux   2
foo   corge  3
bar   qux    4
bar   quux   5
bar   corge  6
baz   quux   7
baz   corge  8

For every group in ID1, starting at the lowest index values, I want to pick the match from ID2 with the minimum index value for this partition (here: qux with an in index value of 1). Now, for ID1=bar, the matching ID2 would be quux (with an index value of 5), since qux has already been picked (and matched to foo). Similarly, baz would match with corgebecause quux was already matched to bar.

So the resulting query result should be

ID1   ID2   index
foo   qux    1
bar   quux   5
baz   corqe  8

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?

Edit: the values of ID1 and ID2 don't have any meaning, they are just completely random identifiers. I modified the example to make this clearer.

Edit2: the solution from @p3consulting looked very promising, but it doesn't work with the following data

ID1   ID2    index
a1    b1     1
a1    b2     2
a2    b3     3
a4    b4     4

where it only delivers index=1 and not index=3.

data-manipulation oracle select sql
4 Answers

But also need to keep (id1,id2) with MIN(idx), so eventually :

with data(id1, id2, idx)
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
    Union all Select 'a3' as ID1, 'b2' as ID2, 9 as "index" from dual
    Union all Select 'a3' as ID1, 'b3' as ID2, 10 as "index" from dual
)
SELECT id1, id2, idx FROM (
    SELECT d.*, 
        MIN(idx) KEEP(DENSE_RANK FIRST ORDER BY idx) OVER(PARTITION BY id1, id2) AS midx,
        DENSE_RANK() OVER(ORDER BY id1) AS rn1
        , DENSE_RANK() OVER(ORDER BY id2) AS rn2
    FROM data d
)
WHERE rn1 = rn2 AND idx = midx
ORDER BY idx
;

a1  b1  1
a2  b2  5
a3  b3  10
p3consulting0

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
user199506270

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.

p3consulting0

I don't think this is practical to do in Oracle SQL. You might get a wizard with extra time on his hands to post something really cool using the MODEL clause or similar feature, but if this is practical code that you want other people to be able to read, understand, and maintain, I would do this in PL/SQL.

Here's the general idea (this won't compile exactly, but it's pretty close).

DECLARE
  l_rc SYS_REFCURSOR;

BEGIN
  -- Mark everything unused to start
  -- You need a "USED" column in your table.
  UPDATE my_table
  SET used = 'N';

  LOOP  
    BEGIN
      SELECT id1, id2, idx
      INTO   l_id1, l_id2, l_idx
      WHERE used = 'N'
      ORDER BY IDX
      FETCH FIRST 1 ROW ONLY;
    EXCEPTION
      WHEN no_data_found THEN
        EXIT; -- exit loop
    END;
  
    -- Mark the row as used = 'Y'...these will be in our result set
    UPDATE my_table
    SET    used = 'Y'
    WHERE  idx = l_idx;

    -- Mark all the other occurrences of id2 as 'X' to make them ineligible in
    -- future iterations
    UPDATE my_table
    SET    used = 'X'
    WHERE  used IS NULL
    AND    id2 = l_id2;
  END LOOP;

  OPEN l_rc FOR 
    SELECT id1, id2, idx
    FROM   my_table
    WHERE  used = 'Y'
    ORDER BY idx;
  
  RETURN l_rc;
END;

If you really need to provide a SQL API to this logic (i.e., you really want other code to be able to SELECT the answer instead of, for example, getting a ref cursor output from a PL/SQL procedure, you can wrap the PL/SQL logic in a pipelined function and, optionally, wrap that in a view.

Matthew McPeak0
Related