Forum Stats

  • 3,751,469 Users
  • 2,250,366 Discussions
  • 7,867,434 Comments

Discussions

Find diagonal values of a 3*3 table

rajeshksethi
rajeshksethi Member Posts: 2 Red Ribbon

Hi All,

Pleas help me to find the values from a 3 * 3 table (A table having 3 columns and 3 rows).


-- Sample Table

create table tab(col1 number, col2 number, col3 number);

-- Sample Data

insert into tab values(1,2,3);

insert into tab values(4,5,6);

insert into tab values(7,8,9);

commit;

select * from tab;

-- Output

1,5,9


Thanks,

Rajesh

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,670 Black Diamond
    Accepted Answer

    And if you want one row back:

    with t as (
               select  tab.*,
                       row_number() over(order by col1) rn
                 from  tab
              )
    select  min(col1) col1,
            max(
                case rn
                  when 2 then col2
                end
               ) col2,
            max(col3) col3
      from  t
    /
    
          COL1       COL2       COL3
    ---------- ---------- ----------
             1          5          9
    
    SQL>
    

    SY.

    rajeshksethi
  • mathguy
    mathguy Member Posts: 9,990 Gold Crown
    Accepted Answer

    In a relational database, rows in a table are not ordered in any pre-defined way; a table is an unordered set of rows.

    In fact, at least some of the founders of relational database theory say that the column order is not determined either; this contradicts set theory, in which the order of factors is relevant in the definition of a Cartesian product, and therefore in the definition of any relation (a "relation" is simply a subset of a Cartesian product of sets).

    This is a long way to explain why your problem is not well-defined. "Diagonal" means elements where the row number and the column number are equal. Even if we take the more restricted view, that columns in a table are in a well-defined order, rows are not - so it is not clear what you mean by "diagonal".

    Now, let's assume that in addition to the data you gave us, there is an additional column ord that indicates row order. We must assume that the values in this column are all distinct and non-null. Of course, we must also assume that the number of rows equals the number of columns (if we exclude the additional ord column). If so:

    -- Sample Table
    create table tab(ord number, col1 number, col2 number, col3 number);
    
    -- Sample Data
    insert into tab values(101,1,2,3);
    insert into tab values(106,4,5,6);
    insert into tab values(108,7,8,9);
    commit;
    
    -- Query and Output
    
    select min(case when rownum = 1 then col1 end) as diag1,
           min(case when rownum = 2 then col2 end) as diag2,
           min(case when rownum = 3 then col3 end) as diag3
    from   (
             select col1, col2, col3
             from   tab
             order  by ord
           )
    ;
    
         DIAG1      DIAG2      DIAG3
    ---------- ---------- ----------
             1          5          9
    

    Note that the ord values are not 1, 2, 3 - they can be anything (they can even be dates, or strings) - the only condition is that they are all distinct and non-null, so that they give us a well-defined total ordering of the rows. Note also that rownum must be referenced outside the query where we order the input rows by ord - we do need the subquery, this can't be done in a single pass. (It can, with more advanced - and probably slower, in this type of problem - features of the language, for example with the match_recognize clause.)

    Also, the aggregate functions (min in this case) are needed, even if you may not see why. The query aggregates data from several rows into a single row; even if we don't need the group by clause, since we are aggregating over the entire table, the use of aggregate functions is required (by the SQL language standard and by the Oracle implementation).

    Now, to the bigger question: Where is all this coming from? I am willing to bet that this is not your original question, but rather just a "last step" in your attempted solution to a different problem. And I am willing to bet that the problem has to do with pivoting. But I hate speculation. Please tell us where this is coming from.

    rajeshksethi

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,670 Black Diamond
    select  case row_number() over(order by col1)
              when 1 then col1
              when 2 then col2
              else col3
            end col
      from  tab
      order by row_number() over(order by col1)
    /
    
           COL
    ----------
             1
             5
             9
    
    SQL>
    
    

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,670 Black Diamond
    Accepted Answer

    And if you want one row back:

    with t as (
               select  tab.*,
                       row_number() over(order by col1) rn
                 from  tab
              )
    select  min(col1) col1,
            max(
                case rn
                  when 2 then col2
                end
               ) col2,
            max(col3) col3
      from  t
    /
    
          COL1       COL2       COL3
    ---------- ---------- ----------
             1          5          9
    
    SQL>
    

    SY.

    rajeshksethi
  • mathguy
    mathguy Member Posts: 9,990 Gold Crown
    Accepted Answer

    In a relational database, rows in a table are not ordered in any pre-defined way; a table is an unordered set of rows.

    In fact, at least some of the founders of relational database theory say that the column order is not determined either; this contradicts set theory, in which the order of factors is relevant in the definition of a Cartesian product, and therefore in the definition of any relation (a "relation" is simply a subset of a Cartesian product of sets).

    This is a long way to explain why your problem is not well-defined. "Diagonal" means elements where the row number and the column number are equal. Even if we take the more restricted view, that columns in a table are in a well-defined order, rows are not - so it is not clear what you mean by "diagonal".

    Now, let's assume that in addition to the data you gave us, there is an additional column ord that indicates row order. We must assume that the values in this column are all distinct and non-null. Of course, we must also assume that the number of rows equals the number of columns (if we exclude the additional ord column). If so:

    -- Sample Table
    create table tab(ord number, col1 number, col2 number, col3 number);
    
    -- Sample Data
    insert into tab values(101,1,2,3);
    insert into tab values(106,4,5,6);
    insert into tab values(108,7,8,9);
    commit;
    
    -- Query and Output
    
    select min(case when rownum = 1 then col1 end) as diag1,
           min(case when rownum = 2 then col2 end) as diag2,
           min(case when rownum = 3 then col3 end) as diag3
    from   (
             select col1, col2, col3
             from   tab
             order  by ord
           )
    ;
    
         DIAG1      DIAG2      DIAG3
    ---------- ---------- ----------
             1          5          9
    

    Note that the ord values are not 1, 2, 3 - they can be anything (they can even be dates, or strings) - the only condition is that they are all distinct and non-null, so that they give us a well-defined total ordering of the rows. Note also that rownum must be referenced outside the query where we order the input rows by ord - we do need the subquery, this can't be done in a single pass. (It can, with more advanced - and probably slower, in this type of problem - features of the language, for example with the match_recognize clause.)

    Also, the aggregate functions (min in this case) are needed, even if you may not see why. The query aggregates data from several rows into a single row; even if we don't need the group by clause, since we are aggregating over the entire table, the use of aggregate functions is required (by the SQL language standard and by the Oracle implementation).

    Now, to the bigger question: Where is all this coming from? I am willing to bet that this is not your original question, but rather just a "last step" in your attempted solution to a different problem. And I am willing to bet that the problem has to do with pivoting. But I hate speculation. Please tell us where this is coming from.

    rajeshksethi
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,670 Black Diamond
    edited Jun 19, 2021 7:52PM

    Now keep in mind, there is no row order in relational tables and solution I posted assumes col1  <= col2 <= col3 in each row and and is sequencing rows in col1 order.

    SY.

    rajeshksethi
  • rajeshksethi
    rajeshksethi Member Posts: 2 Red Ribbon

    Hi @mathguy,

    Thanks for the explanation and the solution. It works.

    You are right. For this type of problems, we must have a column where the insertion order can be preserved and using that column we can find the diagonals.

    Hi @Solomon Yakobson,

    Thanks for the solution. It works too.

    Thanks,

    Rajesh