## Forum Stats

• 3,757,039 Users
• 2,251,189 Discussions

Discussions

# Find diagonal values of a 3*3 table

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

• 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.

• 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.

• ```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.

• 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.

• 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.

• 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.

• 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.

Thanks for the solution. It works too.

Thanks,

Rajesh