Forum Stats

  • 3,757,041 Users
  • 2,251,189 Discussions
  • 7,869,700 Comments

Discussions

Categories

Find diagonal values of a 3*3 table

rajeshksethi
rajeshksethi Member Posts: 2 Red Ribbon
in SQL & PL/SQL

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

Tagged:
· Share on TwitterShare on Facebook

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,716 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
    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,063 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
    · Share on TwitterShare on Facebook

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,716 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.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,716 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
    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,063 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
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,716 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
    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook