Discussions
Categories
 196K All Categories
 2.1K Data
 208 Big Data Appliance
 1.9K Data Science
 447.4K Databases
 220.8K General Database Discussions
 3.7K Java and JavaScript in the Database
 23 Multilingual Engine
 516 MySQL Community Space
 463 NoSQL Database
 7.8K Oracle Database Express Edition (XE)
 2.9K ORDS, SODA & JSON in the Database
 469 SQLcl
 3.9K SQL Developer Data Modeler
 185.9K SQL & PL/SQL
 20.9K SQL Developer
 292.6K Development
 7 Developer Projects
 124 Programming Languages
 289.4K Development Tools
 95 DevOps
 3K QA/Testing
 645.5K Java
 23 Java Learning Subscription
 36.9K Database Connectivity
 150 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.8K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 15 Java Essentials
 143 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 197 Java User Groups
 24 JavaScript  Nashorn
 Programs
 238 LiveLabs
 35 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.8K Other Languages
 2.3K Chinese
 166 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 225 Portuguese
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
Best Answers

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 predefined 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 welldefined. "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 welldefined 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 nonnull. Of course, we must also assume that the number of rows equals the number of columns (if we exclude the additionalord
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 nonnull, so that they give us a welldefined total ordering of the rows. Note also thatrownum
must be referenced outside the query where we order the input rows byord
 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 thematch_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 thegroup 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.
Answers

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 predefined 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 welldefined. "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 welldefined 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 nonnull. Of course, we must also assume that the number of rows equals the number of columns (if we exclude the additionalord
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 nonnull, so that they give us a welldefined total ordering of the rows. Note also thatrownum
must be referenced outside the query where we order the input rows byord
 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 thematch_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 thegroup 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