Re: Sorting multiple rows

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 10 Jan 2002 05:55:31 -0800
Message-ID: <a1k6gj02qak@drn.newsguy.com>

In article <k46%7.265616$BX4.15157563_at_e3500-atl1.usenetserver.com>, "Ryn" says...
>
>Hello folks,
>
>I have hundreds of rows of data in a format simialr to:
>
>column 1 - column 7
>2001-12-05, 12, 34, 06, 56, 33, 98
>1999-09-03, 01, 06, 34, 57, 23, 12
>.
>.
>.

one approach is:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t   2 ( c1 varchar2(15), c2 int, c3 int, c4 int, c5 int, c6 int, c7 int );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values( '2001-12-05', 12, 34, 06, 56, 33, 98 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values( '1999-09-03', 01, 06, 34, 57, 23, 12 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select decode( r, 2, c2, 3, c3, 4, c4, 5, c5,

6, c6, 7, c7 ), count(*)
  2 from t, ( select rownum+1 r from all_objects where rownum <= 6 )   3 group by decode( r, 2, c2, 3, c3, 4, c4, 5, c5, 6, c6, 7, c7 );

DECODE(R,2,C2,3,C3,4,C4,5,C5,6,C6,7,C7) COUNT(*)

--------------------------------------- ----------
                                      1          1
                                      6          2
                                     12          2
                                     23          1
                                     33          1
                                     34          2
                                     56          1
                                     57          1
                                     98          1

9 rows selected.

basically, you need to turn each row in T into 6 rows, sort of like a transposition and then just count...

You could do it with a bunch of union all's but this will only make one pass on T instead of 6 passes as the union all's would

>I need to select column 2 - column 7 and print something similar to:
>
>12: 6 entries
>34: 3 entries
>23: 2 entries
>01: 2 entries
>08: 1 entries
>55: 1 entries
>
>I have been parsing through the SQL documentation and cannot find an
>example that does something similar. Does anyone have an example they
>could share?
>
>Thanks,
>
>- Ryan
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Jan 10 2002 - 07:55:31 CST