SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Query Help

Achyut KOct 1 2010 — edited Oct 21 2010

Hi all,

I have one requirement in which I want the output of one column in ascending order and desc order in seprate columns.

For Examaple

select '1' as COL from dual
union all
select '2' from dual
union all
select '3' from dual
union all
select '4' from dual

Output  Should be
COL COL1
1      4
2      3
3      2
4      1

Regards,
Achyut

Frank Kulash

Hi,

That's an example of a Prix-Fixe Query , where each row does not represent any real entity.
Here's one way to do it:

WITH	got_nums	AS
(
	SELECT	col
	,	ROW_NUMBER () OVER (ORDER BY col ASC )	AS a_num
	,	ROW_NUMBER () OVER (ORDER BY col DESC)	AS d_num
	FROM	table_x
)
SELECT	  a.col
,	  d.col	AS col1
FROM	  got_nums	a
JOIN	  got_nums	d	ON	a.a_num	= d.d_num
ORDER BY  a.a_num
;

kordirko

Try this

WITH src AS(
  SELECT '1' AS COL FROM dual
  UNION ALL
  select '2' from dual
  union all
  SELECT '3' FROM dual
  UNION ALL
  SELECT '4' FROM dual
),
SRC1 AS(
  SELECT row_number() OVER( ORDER BY col) rn1,
       row_number() OVER( ORDER BY col DESC) rn2,
       col
  FROM src
)
SELECT s1.col col1,
       s2.col col2
FROM src1 s1
JOIN src1 s2
ON s1.rn1 = s2.rn2
;


COL1 COL2 
---- ---- 
1    4    
2    3    
3    2    
4    1

Aketi Jyuuzou

I like nth_value B-)

col ascCol  for a10
col descCol for a10

with t(col) as(
select '1' from dual union all
select '2' from dual union all
select '3' from dual union all
select '4' from dual)
select
nth_value(col,RowNum) from Last over(order by col desc
Rows between Unbounded Preceding and Unbounded Following) as ascCol,
nth_value(col,RowNum) from Last over(order by col
Rows between Unbounded Preceding and Unbounded Following) as descCol
from t;

ascCol  descCol
------  -------
1       4
2       3
3       2
4       1

SomeHow we have to use "from Last" of nth_value :-(
1118408

munky

>
I like nth_value
>
Aketi

You forgot to mention that that's in 11gR2 only...

I'd like it too if my current client would upgrade! ;)

Cheers

Ben

789895

Hi,

WITH D AS (
  SELECT '1' AS COL 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
)
SELECT COL1, COL2 FROM (SELECT ROW_NUMBER() OVER (ORDER BY COL desc) COL2, ROW_NUMBER() OVER (ORDER BY COL ) COL1 FROM D)

cheers

VT

BluShadow

VT wrote:
Hi,

WITH D AS (
SELECT '1' AS COL 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
)
SELECT COL1, COL2 FROM (SELECT ROW_NUMBER() OVER (ORDER BY COL desc) COL2, ROW_NUMBER() OVER (ORDER BY COL ) COL1 FROM D)

cheers

VT

That's fine assuming the OP was being completely literal in his requirement of needing numbers sequentially from 1 onwards, but I would guess that was just example data to demonstrate the requirement of having one set of values ascending paired with the descending equivalents. Your solution won't work with other values...

SQL> ed
Wrote file afiedt.buf

  1  WITH D AS (
  2    SELECT 'A' AS COL FROM DUAL
  3    UNION ALL
  4    SELECT 'B' FROM DUAL
  5    UNION ALL
  6    SELECT 'C' FROM DUAL
  7    UNION ALL
  8    SELECT 'D' FROM DUAL
  9    UNION ALL
 10    SELECT 'E' FROM DUAL
 11    UNION ALL
 12    SELECT 'F' FROM DUAL
 13  )
 14* SELECT COL1, COL2 FROM (SELECT ROW_NUMBER() OVER (ORDER BY COL desc) COL2, ROW_NUMBER() OVER (ORDER BY COL ) COL1 FROM D)
SQL> /

      COL1       COL2
---------- ----------
         1          6
         2          5
         3          4
         4          3
         5          2
         6          1

6 rows selected.

SQL>

803755

You can use union operator for this.........
Oracle

737905

Try this:

SQL> ed
Wrote file afiedt.buf

  1  with t as (select 1 col1 from dual
  2  union select 2 from dual
  3  union select 3 from dual
  4  union select 4 from dual
  5  union select 5 from dual
  6  union select 6 from dual
  7  union select 7 from dual
  8  union select 8 from dual
  9  union select 9 from dual
 10  union select 10 from dual
 11  union select 11 from dual
 12  union select 12 from dual)
 13  SELECT col1,MAX(col1) OVER() + 1 - col1 Reverse_ord FROM
 14* t
SQL> /

      COL1 REVERSE_ORD
---------- -----------
         1          12
         2          11
         3          10
         4           9
         5           8
         6           7
         7           6
         8           5
         9           4
        10           3
        11           2
        12           1

12 rows selected.

SQL>

ED: This would not work if the numbers are not in sequence

Edited by: AP on Oct 7, 2010 6:07 AM

Aketi Jyuuzou

You have posted nice sample BluShadow :D

col ascCol  for a10
col descCol for a10

WITH D AS (
SELECT 'A' AS COL FROM DUAL UNION ALL
SELECT 'B' FROM DUAL UNION ALL
SELECT 'C' FROM DUAL UNION ALL
SELECT 'D' FROM DUAL UNION ALL
SELECT 'E' FROM DUAL UNION ALL
SELECT 'F' FROM DUAL)
select
nth_value(col,RowNum) from Last over(order by col desc
Rows between Unbounded Preceding and Unbounded Following) as ascCol,
nth_value(col,RowNum) from Last over(order by col
Rows between Unbounded Preceding and Unbounded Following) as descCol
from D;

ascCol  descCol
------  -------
A       F
B       E
C       D
D       C
E       B
F       A

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 5 2010

Added on Oct 1 2010

9 comments

861 views

SQL & PL/SQL

SQL Query Help

Comments

Post Details