All geek questions in one place

Determining the highest number in a comma using Oracle SQL

I have a table with two columns:

OLD_REVISIONS   |NEW_REVISIONS
-----------------------------------
1,25,26,24      |1,26,24,25
1,56,55,54      |1,55,54
1               |1
1,2             |1
1,96,95,94      |1,96,94,95
1               |1
1               |1
1               |1
1               |1
1,2             |1,2
1               |1
1               |1
1               |1
1               |1

  • For each line there will be a list of changes for the document (separated by a comma)

  • The comma separated list may be the same in both columns, but the order / sorting may be different - for example,

    2,1 |1,2

I would like to find all instances where the highest revision in the OLD_REVISIONS column is lower than the highest version in NEW_REVISIONS

The following criteria will meet these criteria.

OLD_REVISIONS   |NEW_REVISIONS
-----------------------------------
1,2             |1
1,56,55,54      |1,55,54

  • I tried the solution using the MINUS option (join the table to myself), but it returns differences even if the list is the same, but in the wrong order

  • GREATEST (.. greatest(new_Revisions) < greatest(old_revisions)), , (OLD_REVISIONS) , . . , , VARCHAR.

, MAX .

? SQL, ( PL/SQL-, )

, NEW_REVISIONS , :

"DOCNUMBER" "REVISIONNUMBER"
67          1
67          24
67          25
67          26
75          1
75          54
75          55
75          56
78          1
79          1
79          2
83          1
83          96
83          94

, , . , , , , .

, , :

docnumber, count, revisions

, , listagg.

, , - , , , MINUS, , .

, , .

, , ( - , out max , )

+4
7

Enjoy.

select    xmlcast(xmlquery(('max((' || OLD_REVISIONS || '))') RETURNING CONTENT) as int) as OLD_REVISIONS_max
         ,xmlcast(xmlquery(('max((' || NEW_REVISIONS || '))') RETURNING CONTENT) as int) as NEW_REVISIONS_max

from      t
;
+3

- regexp_substr , max min.

Sample Demo

with rownums as (select t.*,row_number() over(order by old_revisions) rn from t)
select old_revisions,new_revisions 
from rownums 
where rn in (select rn
             from rownums
             group by rn
             connect by regexp_substr(old_revisions, '[^,]+', 1, level) is not null 
             or regexp_substr(new_revisions, '[^,]+', 1, level) is not null
             having max(cast(regexp_substr(old_revisions,'[^,]+', 1, level) as int)) 
              <> max(cast(regexp_substr(new_revisions,'[^,]+', 1, level) as int))
    )
+2

, id ( ?) - , .

. , vkp, . , .

with
     t ( id, old_revisions, new_revisions ) as (
       select 101, '1,25,26,24', '1,26,24,25' from dual union all
       select 102, '1,56,55,54', '1,55,54'    from dual union all
       select 103, '1'         , '1'          from dual union all
       select 104, '1,2'       , '1'          from dual union all
       select 105, '1,96,95,94', '1,96,94,95' from dual union all
       select 106, '1'         , '1'          from dual union all
       select 107, '1'         , '1'          from dual union all
       select 108, '1'         , '1'          from dual union all
       select 109, '1'         , '1'          from dual union all
       select 110, '1,2'       , '1,2'        from dual union all
       select 111, '1'         , '1'          from dual union all
       select 112, '1'         , '1'          from dual union all
       select 113, '1'         , '1'          from dual union all
       select 114, '1'         , '1'          from dual
       )
--   END of TEST DATA; the actual solution (SQL query) begins below.
select id, old_revisions, new_revisions
from (
    select id, old_revisions, new_revisions, 'old' as flag,
           to_number(regexp_substr(old_revisions, '\d+', 1, level)) as rev_no
      from t
      connect by level <= regexp_count(old_revisions, ',') + 1
         and  prior id = id
          and prior sys_guid() is not null
    union all
    select id, old_revisions, new_revisions, 'new' as flag,
           to_number(regexp_substr(new_revisions, '\d+', 1, level)) as rev_no
      from t
      connect by level <= regexp_count(new_revisions, ',') + 1
         and  prior id = id
          and prior sys_guid() is not null
     )
group by id, old_revisions, new_revisions
having max(case when flag = 'old' then rev_no end) !=
       max(case when flag = 'new' then rev_no end)
order by id           --   ORDER BY is optional
;


 ID OLD_REVISION NEW_REVISION
--- ------------ ------------
102 1,56,55,54   1,55,54   
104 1,2          1
+2

, . , , . - :

select greatest(val1, val2), t1.r from (
select max(val) val1, r from (
select regexp_substr(v1,'[^,]+', 1, level) val, rowid r from tab1
  connect by regexp_substr(v1, '[^,]+', 1, level) is not null
  ) group by r) t1
  inner join (
select max(val) val2, r from (
select regexp_substr(v2,'[^,]+', 1, level) val, rowid r from tab1
  connect by regexp_substr(v2, '[^,]+', 1, level) is not null
  ) group by r) t2
  on (t1.r = t2.r);

:

create table tab1 (v1 varchar2(100), v2 varchar2(100));
insert into tab1 values ('1,3,5','1,4,7');
insert into tab1 values ('1,3,5','1,2,9');
insert into tab1 values ('1,3,5','1,3,5');
insert into tab1 values ('1,3,5','1,4');

, , . rowid . , - .

:

select greatest(val1, val2), t1.r from (
select max(val) val1, r from (
select regexp_substr(v1,'[^,]+', 1, level) val, DOCNUMBER r from tab1
  connect by regexp_substr(v1, '[^,]+', 1, level) is not null
  ) group by DOCNUMBER) t1
  inner join (
select max(DOCNUMBER) val2, DOCNUMBER r from NEW_REVISIONS) t2
  on (t1.r = t2.r);
+1

, , listagg.

SELECT listagg(o,',') WITHIN GROUP (ORDER BY o) old_revisions,
       listagg(n,',')  WITHIN GROUP (ORDER BY n) new_revisions
FROM (
     SELECT DISTINCT  rowid r,
            regexp_substr(old_revisions, '[^,]+', 1, LEVEL) o,
            regexp_substr(new_revisions, '[^,]+', 1, LEVEL) n
     FROM   table
     WHERE  regexp_substr(old_revisions, '[^,]+', 1, LEVEL) IS NOT NULL
     CONNECT BY LEVEL<=(SELECT greatest(MAX(regexp_count(old_revisions,',')),MAX(regexp_count(new_revisions,',')))+1 c FROM table)
     )
GROUP BY r
HAVING listagg(o,',') WITHIN GROUP (ORDER BY o)<>listagg(n,',') WITHIN GROUP (ORDER BY n);
+1

:

select 
  OLD_REVISIONS,
  NEW_REVISIONS
from 
  REVISIONS t,
  table(cast(multiset(
                        select level
                        from dual
                        connect by  level <= length (regexp_replace(t.OLD_REVISIONS, '[^,]+'))  + 1
                      ) as sys.OdciNumberList
             )
       ) levels_old,
  table(cast(multiset(
                        select level
                        from dual
                        connect by  level <= length (regexp_replace(t.NEW_REVISIONS, '[^,]+'))  + 1
                     )as sys.OdciNumberList
            )
       ) levels_new
group by t.ROWID,
  OLD_REVISIONS,
  NEW_REVISIONS
having max(to_number(trim(regexp_substr(t.OLD_REVISIONS, '[^,]+', 1, levels_old.column_value)))) >
       max(to_number(trim(regexp_substr(t.new_REVISIONS, '[^,]+', 1, levels_new.column_value))))

, , . , GROUP BYclause rowid, .

+1

PL/SQL-,

select  max_num( '1,26,24,25') max_num from dual;
   MAX_NUM
----------
        26

ist :

select OLD_REVISIONS  NEW_REVISIONS
from revs
where max_num(OLD_REVISIONS) < max_num(NEW_REVISIONS);


create or replace function max_num(str_in VARCHAR2) return NUMBER as 
i number;
x varchar2(1);
n number := 0;
max_n number := 0;
pow number := 0;
begin
 for i in 0.. length(str_in)-1 loop
  x := substr(str_in,length(str_in)-i,1);
  if x = ',' then 
    -- check max number
    if n > max_n then 
       max_n := n;
    end if;   
    -- reset
    n := 0;
    pow := 0;
  else
    n := n + to_number(x)*power(10,pow);
    pow := pow +1;
  end if;
 end loop;
 return(max_n);
end;
/
0

Source: https://habr.com/ru/post/1658695/

More articles:


All Articles