Thanks for the question.

Asked: June 04, 2013 - 5:57 am UTC

Last updated: June 05, 2013 - 2:09 pm UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hello Tom,

I have table T which contains in column X the values 1 .. 10.

CREATE TABLE T ( X  NUMBER);

Insert into T (X) Values (1);
Insert into T (X) Values (2);
Insert into T (X) Values (3);
Insert into T (X) Values (4);
Insert into T (X) Values (5);
Insert into T (X) Values (6);
Insert into T (X) Values (7);
Insert into T (X) Values (8);
Insert into T (X) Values (9);
Insert into T (X) Values (10);

I need a select which retrieves the column values in alternating order depending from the size:
1, 10, 2, 9, 3, 8, 4, 7, 5, 6

Do you have some elegant solution?

Thanks
Peter

and Tom said...

it'll take some resources to do this! on a large set, be prepared for that.


ops$tkyte%ORA11GR2> select x, up_rank, down_rank, cnt,
  2         case when up_rank <= cnt/2
  3              then up_rank
  4              else down_rank+0.5
  5          end rnk
  6    from (
  7  select x, row_number() over (order by x) up_rank, row_number() over (order by x desc) down_rank, count(*) over () cnt
  8    from t
  9         )
 10   order by rnk
 11  /

         X    UP_RANK  DOWN_RANK        CNT        RNK
---------- ---------- ---------- ---------- ----------
         1          1         10         10          1
        10         10          1         10        1.5
         2          2          9         10          2
         9          9          2         10        2.5
         3          3          8         10          3
         8          8          3         10        3.5
         4          4          7         10          4
         7          7          4         10        4.5
         5          5          6         10          5
         6          6          5         10        5.5

10 rows selected.

ops$tkyte%ORA11GR2> Insert into T (X) Values (11);

1 row created.

ops$tkyte%ORA11GR2> select x, up_rank, down_rank, cnt,
  2         case when up_rank <= cnt/2
  3              then up_rank
  4              else down_rank+0.5
  5          end rnk
  6    from (
  7  select x, row_number() over (order by x) up_rank, row_number() over (order by x desc) down_rank, count(*) over () cnt
  8    from t
  9         )
 10   order by rnk
 11  /

         X    UP_RANK  DOWN_RANK        CNT        RNK
---------- ---------- ---------- ---------- ----------
         1          1         11         11          1
        11         11          1         11        1.5
         2          2         10         11          2
        10         10          2         11        2.5
         3          3          9         11          3
         9          9          3         11        3.5
         4          4          8         11          4
         8          8          4         11        4.5
         5          5          7         11          5
         7          7          5         11        5.5
         6          6          6         11        6.5

11 rows selected.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Without row_number

Santha Kumar Nowpada, June 05, 2013 - 6:26 am UTC

Tom's solution is great. I tried doing it as below without using row_number. There can be a different solution using model clause.

with t2 as
(
select rownum r, x
from (select x from t order by x)
)
select a.*,
(select b.val from t2 b where b.r = decode(mod(a.r,2), 0, (select count(x)+1 from t)-a.r/2, (a.r+1)/2)) chk
from t2 a

June 05, 2013 - 1:44 pm UTC

b.val doesn't work in there. and this isn't a model clause :)

and the performance of it as it stands is "not very good"

ops$tkyte%ORA11GR2> CREATE TABLE T ( X  NUMBER);

Table created.

Elapsed: 00:00:00.01
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t select level from dual connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.06
ops$tkyte%ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.00
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly
ops$tkyte%ORA11GR2> select x, up_rank, down_rank, cnt,
  2            case when up_rank <= cnt/2
  3                 then up_rank
  4                 else down_rank+0.5
  5             end rnk
  6       from (
  7     select x, row_number() over (order by x) up_rank, row_number() over (order by x desc) down_rank, count(*) over () cnt
  8       from t
  9            )
 10    order by rnk
 11  /

10000 rows selected.

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 3221677900

--------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 10000 |   507K|       |   143   (3)| 00:00:02 |
|   1 |  SORT ORDER BY        |      | 10000 |   507K|   640K|   143   (3)| 00:00:02 |
|   2 |   VIEW                |      | 10000 |   507K|       |    10  (30)| 00:00:01 |
|   3 |    WINDOW SORT        |      | 10000 | 40000 |       |    10  (30)| 00:00:01 |
|   4 |     WINDOW SORT       |      | 10000 | 40000 |       |    10  (30)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T    | 10000 | 40000 |       |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
     299418  bytes sent via SQL*Net to client
       7745  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      10000  rows processed

ops$tkyte%ORA11GR2> with t2 as
  2  (
  3  select rownum r, x
  4  from (select x from t order by x)
  5  )
  6  select a.*,
  7         (select b.x from t2 b where b.r = decode(mod(a.r,2), 0, (select count(x)+1 from t)-a.r/2, (a.r+1)/2)) chk
  8  from t2 a
  9  /

10000 rows selected.

Elapsed: 00:01:54.75

Execution Plan
----------------------------------------------------------
Plan hash value: 1738207458

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            | 10000 |   253K|    11  (10)| 00:00:01 |
|*  1 |  VIEW                      |                            | 10000 |   253K|     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D66B1_6FF4833 | 10000 | 40000 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE           |                            |     1 |     4 |            |          |
|   4 |    TABLE ACCESS FULL       | T                          | 10000 | 40000 |     7   (0)| 00:00:01 |
|   5 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
|   6 |   LOAD AS SELECT           | SYS_TEMP_0FD9D66B1_6FF4833 |       |       |            |          |
|   7 |    COUNT                   |                            |       |       |            |          |
|   8 |     VIEW                   |                            | 10000 | 40000 |     8  (13)| 00:00:01 |
|   9 |      SORT ORDER BY         |                            | 10000 | 40000 |     8  (13)| 00:00:01 |
|  10 |       TABLE ACCESS FULL    | T                          | 10000 | 40000 |     7   (0)| 00:00:01 |
|  11 |   VIEW                     |                            | 10000 |   253K|     3   (0)| 00:00:01 |
|  12 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D66B1_6FF4833 | 10000 | 40000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("B"."R"=DECODE(MOD(:B1,2),0, (SELECT COUNT("X")+1 FROM "T" "T")-:B2/2,(:B3+1)/2))


Statistics
----------------------------------------------------------
         54  recursive calls
         25  db block gets
     190762  consistent gets
         18  physical reads
        812  redo size
     216596  bytes sent via SQL*Net to client
       7745  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

ops$tkyte%ORA11GR2> set autotrace off

And another way

AndyP, June 05, 2013 - 9:14 am UTC

with data as
(
select 1 x from dual
union all
select 2 from dual
union all
select 3 from dual
union all
select 4 from dual
union all
select 5 from dual
union all
select 6 from dual
union all
select 7 from dual
union all
select 8 from dual
union all
select 9 from dual
union all
select 10 from dual
--union all
--select 11 from dual
)
,ups as
(
select x,rownum rn
from data
order by x
)
,downs as
(
select x,rownum+0.1 rn
from
(
select x
from data
order by x desc
)
)
,joined as
(
select x,rn from ups
union all
select x,rn from downs
)
select x from joined
where rn<=(select (max(x)/2)+.5 from data)
order by rn
/

June 05, 2013 - 2:09 pm UTC

...
,ups as
(
select x,rownum rn
from data
order by x
)
.....

bug alert. that takes the data, assigned rownum to it and sorted. IT MIGHT have appeared to work since the data would appear to come out of the scan in sorted order!

with a fix for that - and the removal of the unnecessary union - you get a result that is almost identical to the row_number() trick

ops$tkyte%ORA11GR2> create table t as select level x from dual connect by level <= 10000;

Table created.

Elapsed: 00:00:00.04
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with
  2  ups as
  3  (
  4  select x,rownum rn_up
  5    from (select *
  6            from t
  7               order by x)
  8  )
  9  ,downs as
 10  (
 11  select x, rn_up, rownum+0.1 rn_dn
 12    from ( select x, rn_up
 13             from ups
 14            order by x desc)
 15  )
 16  select x from downs
 17   where least( rn_up, rn_dn ) <=(select (max(x)/2)+.5 from t)
 18   order by least( rn_up, rn_dn )
 19  /

10000 rows selected.

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
Plan hash value: 1227363965

-------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      | 10000 |   380K|       |   193   (3)| 00:00:03 |
|   1 |  SORT ORDER BY             |      | 10000 |   380K|   520K|   193   (3)| 00:00:03 |
|*  2 |   VIEW                     |      | 10000 |   380K|       |    84   (4)| 00:00:02 |
|   3 |    COUNT                   |      |       |       |       |            |          |
|   4 |     VIEW                   |      | 10000 |   253K|       |    84   (4)| 00:00:02 |
|   5 |      SORT ORDER BY         |      | 10000 |   253K|   368K|    84   (4)| 00:00:02 |
|   6 |       VIEW                 |      | 10000 |   253K|       |     8  (13)| 00:00:01 |
|   7 |        COUNT               |      |       |       |       |            |          |
|   8 |         VIEW               |      | 10000 |   126K|       |     8  (13)| 00:00:01 |
|   9 |          SORT ORDER BY     |      | 10000 |   126K|       |     8  (13)| 00:00:01 |
|  10 |           TABLE ACCESS FULL| T    | 10000 |   126K|       |     7   (0)| 00:00:01 |
|  11 |    SORT AGGREGATE          |      |     1 |    13 |       |            |          |
|  12 |     TABLE ACCESS FULL      | T    | 10000 |   126K|       |     7   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEAST("RN_UP","RN_DN")<= (SELECT MAX("X")/2+.5 FROM "T" "T"))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
        120  consistent gets
         16  physical reads
          0  redo size
     136882  bytes sent via SQL*Net to client
       7746  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      10000  rows processed

Abusing MODEL and LISTAGG

Hoek, June 05, 2013 - 12:35 pm UTC

Just for 'learning purposes' ;) :

SQL> -- taking horizontal output literally 
SQL> -- 1, 10, 2, 9, 3, 8, 4, 7, 5, 6 
SQL> --
SQL> select substr(results, 1, length(results)/2) results
  2  from ( select listagg(str, ',') within group (order by (up)) results
  3         from ( select up
  4                ,      up||','||down str 
  5                from  (select level col from dual connect by level <= 10) t
  6                model 
  7                dimension by (col
  8                             )
  9                measures ( row_number() over (order by col) up
 10                         , row_number() over (order by col desc) down
 11                         )
 12                rules () 
 13              )
 14       );

RESULTS
------------------------------------------------------------------------------------
1,10,2,9,3,8,4,7,5,6

1 row selected.

SQL> -- taking horizontal output literally + one extra row
SQL> -- 1, 11, 2, 10, 3, 9, 4, 8, 5, 7, 6 
SQL> -- 
SQL> select substr(results, 1, length(results)/2) results
  2  from ( select listagg(str, ',') within group (order by (up)) results
  3         from ( select up
  4                ,      up||','||down str 
  5                ,      recs
  6                from   (select level col from dual connect by level <= 11) t
  7                model 
  8                dimension by (col
  9                             )
 10                measures ( row_number() over (order by col) up
 11                         , row_number() over (order by col desc) down
 12                         , count(*) over () recs
 13                         )
 14                rules () 
 15              )
 16       );

RESULTS
------------------------------------------------------------------------------------
1,11,2,10,3,9,4,8,5,7,6

1 row selected.

[confession-mode]
Yeah, it can probably be achieved through the RULES part of the MODEL clause, but didn't succeed (yet)...
Oh well, a fun question anyway!
[/confession-mode]

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Without row_number

And another way

Abusing MODEL and LISTAGG

More to Explore

Analytics

Select results in alternating order depending from the size

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Without row_number

And another way

Abusing MODEL and LISTAGG

More to Explore

Analytics