Forum Stats

  • 3,757,567 Users
  • 2,251,246 Discussions
  • 7,869,867 Comments

Discussions

Logic for Best Matching from possible row...

user481231
user481231 Member Posts: 8
edited Dec 17, 2010 7:59AM in SQL & PL/SQL
Hi All,

For example I have below columns and values in my test table

create table test(level1 number,level2 number,level3 number,level4 number,level5 number,level6 number,level7 number,level8 number)

insert into test values(2,3,4,5,6,7,8,9)
insert into test values(12,13,14,15,16,17,18,19)
insert into test values(5,6,7,8,9,11,13,15)
insert into test values(7,8,9,10,11,12,13,14)
insert into test values(9,10,13 ,14,15,16,17,18)
insert into test values(11,22,27,28,29,30,31,32)

select * from test;

2 3 4 5 6 7 8 9 output should be 9
12 13 14 15 16 17 18 19 output should be 12
5 6 7 8 9 11 13 15 output should be 13
7 8 9 10 11 12 13 14 output should be 12
9 10 13 14 15 16 17 18 output should be 13
11 22 27 28 29 30 31 32 output should be 22

when I input a number Ex:12 for each row If there is any column matching exactly it should be dispalyed
IF NOT the most possible highest matching number for that row should be displayed.

is there any in built functions can we use for this ? If anyboyd having this type of logic
please send to me. I think its similar to vlookup in Excel.

Thanks in advance.
Devender

Best Answer

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Accepted Answer
    Introducing an additional ID column to identify the rows:
    SQL> create table test(id number, level1 number,level2 number,level3 number,level4 number,level5 number,level6 number,level7 number,level8 number)
    /
    Table created.
    
    SQL> insert into test values(1,2,3,4,5,6,7,8,9)
    /
    1 row created.
    
    SQL> insert into test values(2,12,13,14,15,16,17,18,19)
    /
    1 row created.
    
    SQL> insert into test values(3,5,6,7,8,9,11,13,15)
    /
    1 row created.
    
    SQL> insert into test values(4,7,8,9,10,11,12,13,14)
    /
    1 row created.
    
    SQL> insert into test values(5,9,10,13 ,14,15,16,17,18)
    /
    1 row created.
    
    SQL> insert into test values(6, 11,22,27,28,29,30,31,32)
    /
    1 row created.
    
    SQL> select distinct
             id "id",
             nvl (min (case when column_value - 12 >= 0 then column_value end) over (partition by id),
                  max (case when column_value - 12 < 0 then column_value end) over (partition by id))
                "level"
        from test,
             table (sys.odcinumberlist (level1,
                                        level2,
                                        level3,
                                        level4,
                                        level5,
                                        level6,
                                        level7,
                                        level8))
    order by id
    /
            id      level
    ---------- ----------
             1          9
             2         12
             3         13
             4         12
             5         13
             6         22
    
    6 rows selected.

Answers

  • 523861
    523861 Member Posts: 1,608
    given that test data, could you show us what your expected output would be?
  • Lokanath Giri
    Lokanath Giri Member Posts: 764 Silver Badge
    edited Dec 15, 2010 12:26AM
    Query become too big

    use this function to get your desired output

    You can get the least value of the
    select LEAST(ABS(LEVEL1-12),ABS(LEVEL2-12),ABS(LEVEL3-12),ABS(LEVEL4-12),ABS(LEVEL5-12),ABS(LEVEL6-12),ABS(LEVEL7-12),ABS(LEVEL8-12)) Least_Val from test
    /
    
     LEAST_VAL
    ----------
             3                    ----------> 12 + or - 3 = 9 or 15
             0                    ----------> 12 + or - 0 =12    
             1                    ----------> 12 + or - 1 = 11 or 13
             0
             1
             1
  • Lokanath Giri
    Lokanath Giri Member Posts: 764 Silver Badge
    Check this
    select DECODE(ABS(LEVEL1-12),Least_Val,LEVEL1,
                           DECODE(ABS(LEVEL2-12),Least_Val,LEVEL2,
                                 DECODE(ABS(LEVEL3-12),Least_Val,LEVEL3,
                                        DECODE(ABS(LEVEL4-12),Least_Val,LEVEL4, 
                                               DECODE(ABS(LEVEL5-12),Least_Val,LEVEL5,
                                                      DECODE(ABS(LEVEL6-12),Least_Val,LEVEL6,
                                                          DECODE(ABS(LEVEL7-12),Least_Val,LEVEL7,
                                                                 DECODE(ABS(LEVEL8-12),Least_Val,LEVEL8,
                                                                          NULL))))))))     Required_Val   
    FROM
    (            
    select LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL6,LEVEL7,LEVEL8, LEAST(ABS(LEVEL1-12),ABS(LEVEL2-12),ABS(LEVEL3-12),ABS(LEVEL4-12),ABS(LEVEL5-12),ABS(LEVEL6-12),ABS(LEVEL7-12),ABS(LEVEL8-12)) Least_Val from test
    )
    /
    REQUIRED_VAL
    ------------
               9
              12
              11
              12
              13
              11
  • Hi Lokanath,

    Thanks for your time and reply. Did you observe its not exactly coming what I
    was expecting in ouput.

    2 3 4 5 6 7 8 9 for this row output should be 9 where as with ur query output is 9 Correct
    12 13 14 15 16 17 18 19 for this row output should be 12 where as with ur query output is 12 Correct
    5 6 7 8 9 11 13 15 for this row output should be 13 where as with ur query output is 11 Wrong
    7 8 9 10 11 12 13 14 for this row output should be 12 where as with ur query output is 12 Correct
    9 10 13 14 15 16 17 18 for this row output should be 13 where as with ur query output is 13 Correct
    11 22 27 28 29 30 31 32 for this row output should be 22 where as with ur query output is 11 Wrong

    Can you have a look one more time and see my expected output matches.

    Thank you very much.
    Devender
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Accepted Answer
    Introducing an additional ID column to identify the rows:
    SQL> create table test(id number, level1 number,level2 number,level3 number,level4 number,level5 number,level6 number,level7 number,level8 number)
    /
    Table created.
    
    SQL> insert into test values(1,2,3,4,5,6,7,8,9)
    /
    1 row created.
    
    SQL> insert into test values(2,12,13,14,15,16,17,18,19)
    /
    1 row created.
    
    SQL> insert into test values(3,5,6,7,8,9,11,13,15)
    /
    1 row created.
    
    SQL> insert into test values(4,7,8,9,10,11,12,13,14)
    /
    1 row created.
    
    SQL> insert into test values(5,9,10,13 ,14,15,16,17,18)
    /
    1 row created.
    
    SQL> insert into test values(6, 11,22,27,28,29,30,31,32)
    /
    1 row created.
    
    SQL> select distinct
             id "id",
             nvl (min (case when column_value - 12 >= 0 then column_value end) over (partition by id),
                  max (case when column_value - 12 < 0 then column_value end) over (partition by id))
                "level"
        from test,
             table (sys.odcinumberlist (level1,
                                        level2,
                                        level3,
                                        level4,
                                        level5,
                                        level6,
                                        level7,
                                        level8))
    order by id
    /
            id      level
    ---------- ----------
             1          9
             2         12
             3         13
             4         12
             5         13
             6         22
    
    6 rows selected.
  • Thank You very much Michael. Its working.

    Thanks a Lot
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I have made without UnPivot solution B-)
    with test(L1,L2,L3,L4,L5,L6,L7,L8) as(
    select  2, 3, 4, 5, 6, 7, 8, 9 from dual union all
    select 12,13,14,15,16,17,18,19 from dual union all
    select  5, 6, 7, 8, 9,11,13,15 from dual union all
    select  7, 8, 9,10,11,12,13,14 from dual union all
    select  9,10,13,14,15,16,17,18 from dual union all
    select 11,22,27,28,29,30,31,32 from dual)
    select L1,L2,L3,L4,L5,L6,L7,L8,
    case when 12 in(L1,L2,L3,L4,L5,L6,L7,L8) then 12
         when 12 > any(L1,L2,L3,L4,L5,L6,L7,L8)
         then Least(case when L1 < 12 then maxVal else L1 end,
                    case when L2 < 12 then maxVal else L2 end,
                    case when L3 < 12 then maxVal else L3 end,
                    case when L4 < 12 then maxVal else L4 end,
                    case when L5 < 12 then maxVal else L5 end,
                    case when L6 < 12 then maxVal else L6 end,
                    case when L7 < 12 then maxVal else L7 end,
                    case when L8 < 12 then maxVal else L8 end)
         else maxVal end as ExtVal
    from (select L1,L2,L3,L4,L5,L6,L7,L8,
          greatest(L1,L2,L3,L4,L5,L6,L7,L8) as maxVal
          from test);
    
    L1  L2  L3  L4  L5  L6  L7  L8  ExtVal
    --  --  --  --  --  --  --  --  ------
     2   3   4   5   6   7   8   9       9
    12  13  14  15  16  17  18  19      12
     5   6   7   8   9  11  13  15      13
     7   8   9  10  11  12  13  14      12
     9  10  13  14  15  16  17  18      13
    11  22  27  28  29  30  31  32      22
  • 737905
    737905 Member Posts: 804
    Like this:
    with t(L1,L2,L3,L4,L5,L6,L7,L8) as(
    select  2, 3, 4, 5, 6, 7, 8, 9 from dual union all
    select 12,13,14,15,16,17,18,19 from dual union all
    select  5, 6, 7, 8, 9,11,13,15 from dual union all
    select  7, 8, 9,10,11,12,13,14 from dual union all
    select  9,10,13,14,15,16,17,18 from dual union all
    select 11,22,27,28,29,30,31,32 from dual)
    SELECT (CASE WHEN 12 >= max_Value THEN max_value
                    WHEN 12 < max_value AND 12 > ls_val THEN  
                                        (CASE WHEN l2-12 >= 0 THEN l2
                                              WHEN l3-12 >= 0 THEN l3
                                              WHEN l4-12 >= 0 THEN l4
                                              WHEN l5-12 >= 0 THEN l5
                                              WHEN l6-12>= 0 THEN l6
                                              WHEN l7-12 >= 0 THEN l7
                                         END)
                   WHEN 12 = ls_val THEN ls_Val END) out_val FROM                      
    (select l1,l2,l3,l4,l5,l6,l7,l8,GREATEST(l1,l2,l3,l4,l5,l6,l7,l8) max_value
    , LEAST(l1,l2,l3,l4,l5,l6,l7,l8) ls_val from t)
    
    out_val
    ----------
    9
    12
    13
    13
    13
    22
    
    
    SQL>
    with t(L1,L2,L3,L4,L5,L6,L7,L8) as(
    select  2, 3, 4, 5, 6, 7, 8, 9 from dual union all
    select 12,13,14,15,16,17,18,19 from dual union all
    select  5, 6, 7, 8, 9,11,13,15 from dual union all
    select  7, 8, 9,10,11,12,13,14 from dual union all
    select  9,10,13,14,15,16,17,18 from dual union all
    select 11,22,27,28,29,30,31,32 from dual)
    SELECT (CASE WHEN 16 >= max_Value THEN max_value
                    WHEN 16 < max_value AND 16 > ls_val THEN  
                                        (CASE WHEN l2-16 >= 0 THEN l2
                                              WHEN l3-16 >= 0 THEN l3
                                              WHEN l4-16 >= 0 THEN l4
                                              WHEN l5-16 >= 0 THEN l5
                                              WHEN l6-16 >= 0 THEN l6
                                              WHEN l7-16 >= 0 THEN l7
                                         END)
                   WHEN 16 = ls_val THEN ls_Val END) out_val FROM                      
    (select l1,l2,l3,l4,l5,l6,l7,l8,GREATEST(l1,l2,l3,l4,l5,l6,l7,l8) max_value
    , LEAST(l1,l2,l3,l4,l5,l6,l7,l8) ls_val from t)
    
    out_val
    ----------
    
    9
    16
    15
    14
    16
    22
  • Thanks AP and Aketi Jyuuzou.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I like sys.odciNumberList :-)
    with test(L1,L2,L3,L4,L5,L6,L7,L8) as(
    select  2, 3, 4, 5, 6, 7, 8, 9 from dual union all
    select 12,13,14,15,16,17,18,19 from dual union all
    select  5, 6, 7, 8, 9,11,13,15 from dual union all
    select  7, 8, 9,10,11,12,13,14 from dual union all
    select  9,10,13,14,15,16,17,18 from dual union all
    select 11,22,27,28,29,30,31,32 from dual)
    select L1,L2,L3,L4,L5,L6,L7,L8,
    nvl(min(case when 12 <= column_value
                 then column_value  end),
        max(column_value)) as ExtVal
      from test,
           Table(sys.odciNumberList(L1,L2,L3,L4,L5,L6,L7,L8))
    group by L1,L2,L3,L4,L5,L6,L7,L8
    order by L1,L2,L3,L4,L5,L6,L7,L8;
    
    L1  L2  L3  L4  L5  L6  L7  L8  ExtVal
    --  --  --  --  --  --  --  --  ------
     2   3   4   5   6   7   8   9       9
     5   6   7   8   9  11  13  15      13
     7   8   9  10  11  12  13  14      12
     9  10  13  14  15  16  17  18      13
    11  22  27  28  29  30  31  32      22
    12  13  14  15  16  17  18  19      12
This discussion has been closed.