Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2011
    Posts
    18

    Question Unanswered: Compare several columns in the same table

    Hello.

    I'm performing maintenance on an existing query in the database and found the following situation:

    The table has the following column structure:

    Item | Quote | Supplier1 | Supplier2 | Supplier3 | Supplier4 | Supplier5 | Supplier6 | Supplier7 | Supplier8 | Supplier9 | Supplier10 | Value1 | Value2 | Value3 | Value4 | Value5 | Value6 | Value7 | Value8 | Value9 | Value10

    Need to get the two suppliers that offered the lowest prices for a given item and listing.

    One solution I found was to use the PIVOT and UNPIVOT function, but could not filter the two suppliers at lower prices. Another solution was to create various cases and compare values​​. However this solution will require several comparisons.

    Anyone have any other suggestions?

    Oracle 11g.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Could you, perhaps, do some normalization? This appears to be highly de-normalized model.

  3. #3
    Join Date
    Mar 2011
    Posts
    18
    Quote Originally Posted by Littlefoot View Post
    Could you, perhaps, do some normalization? This appears to be highly de-normalized model.
    It's a legacy database. I can't make changes to the table. I know that the structure is not correct. For this reason I am having a lot of problems.

    I need to develop a solution based on the existing model.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    can you make a Global Temporary Table & populate it with data from the existing table?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's another idea: WITH factoring clause (or - create a VIEW based on the same SELECT statement), a little bit of analytic functions and ... the result is as follows.

    Test case:
    Code:
    SQL> create table test
      2    (item number,
      3     supplier1 varchar2(3),
      4     supplier2 varchar2(3),
      5     supplier3 varchar2(3),
      6     value1 number,
      7     value2 number,
      8     value3 number
      9    );
    
    Table created.
    
    SQL> insert into test values (1, 'A', 'B', 'C', 100, 200, 300);
    
    1 row created.
    
    SQL> insert into test values (2, 'D', 'E', 'F', 500, 600, 400);
    
    1 row created.
    
    SQL> select * from test;
    
          ITEM SUP SUP SUP     VALUE1     VALUE2     VALUE3
    ---------- --- --- --- ---------- ---------- ----------
             1 A   B   C          100        200        300
             2 D   E   F          500        600        400
    For item 1, two suppliers with the lowest values are A (100) and B (200).
    For item 2, these are F (400) and D (500).

    Smarter part of the job:
    Code:
    SQL> with
      2    v_test as
      3    (select item, supplier1 sup, value1 val from test
      4     union all
      5     select item, supplier2, value2 from test
      6     union all
      7     select item, supplier3, value3 from test
      8    ),
      9    ranking as
     10    (select
     11       item,
     12       val,
     13      sup,
     14       rank() over (partition by item order by val) rnk
     15     from v_test
     16    )
     17  select item,
     18         sup,
     19        val
     20  from ranking
     21  where rnk <= 2;
    
          ITEM SUP        VAL
    ---------- --- ----------
             1 A          100
             1 B          200
             2 F          400
             2 D          500
    
    SQL>

  6. #6
    Join Date
    Mar 2011
    Posts
    18
    Quote Originally Posted by Littlefoot View Post
    Here's another idea: WITH factoring clause (or - create a VIEW based on the same SELECT statement), a little bit of analytic functions and ... the result is as follows.

    Test case:
    Code:
    SQL> create table test
      2    (item number,
      3     supplier1 varchar2(3),
      4     supplier2 varchar2(3),
      5     supplier3 varchar2(3),
      6     value1 number,
      7     value2 number,
      8     value3 number
      9    );
    
    Table created.
    
    SQL> insert into test values (1, 'A', 'B', 'C', 100, 200, 300);
    
    1 row created.
    
    SQL> insert into test values (2, 'D', 'E', 'F', 500, 600, 400);
    
    1 row created.
    
    SQL> select * from test;
    
          ITEM SUP SUP SUP     VALUE1     VALUE2     VALUE3
    ---------- --- --- --- ---------- ---------- ----------
             1 A   B   C          100        200        300
             2 D   E   F          500        600        400
    For item 1, two suppliers with the lowest values are A (100) and B (200).
    For item 2, these are F (400) and D (500).

    Smarter part of the job:
    Code:
    SQL> with
      2    v_test as
      3    (select item, supplier1 sup, value1 val from test
      4     union all
      5     select item, supplier2, value2 from test
      6     union all
      7     select item, supplier3, value3 from test
      8    ),
      9    ranking as
     10    (select
     11       item,
     12       val,
     13      sup,
     14       rank() over (partition by item order by val) rnk
     15     from v_test
     16    )
     17  select item,
     18         sup,
     19        val
     20  from ranking
     21  where rnk <= 2;
    
          ITEM SUP        VAL
    ---------- --- ----------
             1 A          100
             1 B          200
             2 F          400
             2 D          500
    
    SQL>
    I performed the tests and it still fails.

    Remembering that there are two columns that should be considered: item code and quote number. Another detail, has a list price (quote) of N items.
    Last edited by kbum; 09-19-12 at 14:39. Reason: additional information

  7. #7
    Join Date
    Mar 2011
    Posts
    18
    Test case:

    create table test_supplier
    (quote number,
    item varchar2(15),
    supplier1 varchar2(3),
    supplier2 varchar2(3),
    supplier3 varchar2(3),
    value1 number,
    value2 number,
    value3 number
    );

    insert into test_supplier values (1, 'MAT001', 'A', 'B', 'C', 100, 200, 300);
    insert into test_supplier values (1, 'MAT007', 'A', 'E', 'C', 59, 43, 90);
    insert into test_supplier values (2, 'MAT015', 'B', 'E', 'F', 500, 600, 400);
    insert into test_supplier values (2, 'MAT007', 'A', 'C', 'D', 9, 17, 29);
    insert into test_supplier values (2, 'MAT009', 'D', 'E', 'F', 41, 68, 72);

    select * from test_supplier

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Based on your test case, what do you expect as a result? (I had to create my own as you didn't provide it earlier).

  9. #9
    Join Date
    Mar 2011
    Posts
    18
    Last edited by kbum; 09-19-12 at 16:35. Reason: Formatted result

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No problem; my previous attempt requires slight adjustment.

    Test case again (note that I fixed a typo you've made, based on the desired result; you've got two MAT007 and no MAT004).
    Code:
    SQL> create table test
      2   (quote number,
      3   item varchar2(15),
      4   supplier1 varchar2(3),
      5   supplier2 varchar2(3),
      6   supplier3 varchar2(3),
      7   value1 number,
      8   value2 number,
      9   value3 number
     10   );
    
    Table created.
    
    SQL>
    SQL>  insert into test values (1, 'MAT001', 'A', 'B', 'C', 100, 200, 300);
    
    1 row created.
    
    SQL>  insert into test values (1, 'MAT007', 'A', 'E', 'C', 59, 43, 90);
    
    1 row created.
    
    SQL>  insert into test values (2, 'MAT015', 'B', 'E', 'F', 500, 600, 400);
    
    1 row created.
    
    SQL>  insert into test values (2, 'MAT004', 'A', 'C', 'D', 9, 17, 29);
    
    1 row created.
    
    SQL>  insert into test values (2, 'MAT009', 'D', 'E', 'F', 41, 68, 72);
    
    1 row created.
    
    SQL> select * from test;
    
         QUOTE ITEM            SUP SUP SUP     VALUE1     VALUE2     VALUE3
    ---------- --------------- --- --- --- ---------- ---------- ----------
             1 MAT001          A   B   C          100        200        300
             1 MAT007          A   E   C           59         43         90
             2 MAT015          B   E   F          500        600        400
             2 MAT004          A   C   D            9         17         29
             2 MAT009          D   E   F           41         68         72
    
    SQL>
    A query:
    Code:
    SQL> with
      2    v_test as
      3    (select quote, item, supplier1 sup, value1 val from test
      4     union all
      5     select quote, item, supplier2, value2 from test
      6     union all
      7     select quote, item, supplier3, value3 from test
      8    ),
      9    ranking as
     10    (select
     11       quote,
     12       item,
     13       val,
     14      sup,
     15       rank() over (partition by quote, item order by val) rnk
     16     from v_test
     17    )
     18  select quote,
     19         item,
     20         max(decode(rnk, 1, sup, null)) sup1,
     21         max(decode(rnk, 1, val, null)) val1,
     22         max(decode(rnk, 2, sup, null)) sup2,
     23         max(decode(rnk, 2, val, null)) val2
     24  from ranking
     25  where rnk <= 2
     26  group by quote, item
     27  order by quote, item;
    
         QUOTE ITEM            SUP       VAL1 SUP       VAL2
    ---------- --------------- --- ---------- --- ----------
             1 MAT001          A          100 B          200
             1 MAT007          E           43 A           59
             2 MAT004          A            9 C           17
             2 MAT009          D           41 E           68
             2 MAT015          F          400 B          500
    
    SQL>

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •