ENAME ENAME ---------- ---------- ADAMS JAMES CLARK SCOTT FORD MILLER KING TURNER MARTIN BLAKE SMITH ALLEN WARD JONES
SQL> connect scott/tiger Connected. SQL> select ename from emp order by ename; ENAME ---------- ADAMS ALLEN BLAKE CLARK FORD JAMES JONES KING MARTIN MILLER SCOTT SMITH TURNER WARD 14 rows selected.We need to represent this list of names in two columns. To get started display the result without ordering. Try to use ROWNUM pseudocolumn.
SQL> select rownum rn, ename from emp;
RN ENAME
---------- ----------
1 SMITH
2 ALLEN
3 WARD
4 JONES
5 MARTIN
6 BLAKE
7 CLARK
8 SCOTT
9 KING
10 TURNER
11 ADAMS
12 JAMES
13 FORD
14 MILLER
14 rows selected.
SQL> -- So, we have list of names ordered by ROWNUM pseudocolumn.
SQL> -- Next, we have to divide it by value of ROWNUM - even or odd.
SQL>
SQL> with source_table as
2 (select rownum rn, ename
3 from emp)
4 select tab1.ename, tab2.ename
5 from source_table tab1, source_table tab2
6 where mod(tab1.rn, 2) = 1 -- Odd numbers
7 and tab2.rn = tab1.rn + 1 -- Even numbers
8 ;
ENAME ENAME
---------- ----------
SMITH ALLEN
WARD JONES
MARTIN BLAKE
CLARK SCOTT
KING TURNER
ADAMS JAMES
FORD MILLER
7 rows selected.
SQL> -- Tadaam! But this list is not sorted.
SQL> -- We should try to get another representation,
SQL> -- more beautiful.
SQL> -- Let's rewrite teh query.
SQL>
SQL> with source_table as
2 (select rownum rn, ename
3 from emp)
4 select tab1.ename, tab2.ename
5 from source_table tab1, source_table tab2
6 where mod(tab1.rn, 2) = 1 and tab2.rn = tab1.rn+1
7 order by tab1.ename;
ENAME ENAME
---------- ----------
ADAMS JAMES
CLARK SCOTT
FORD MILLER
KING TURNER
MARTIN BLAKE
SMITH ALLEN
WARD JONES
7 rows selected.
SQL> -- Oooops :-( Certainly, we should use subquery
SQL> -- with order by clause and then
SQL> -- number rows with ROWNUM
SQL> ed
Wrote file afiedt.buf
1 with source_table as
2 (select rownum rn, ename
3 from (select ename
4 from emp
5 order by ename)
6 )
7 select tab1.ename, tab2.ename
8 from source_table tab1, source_table tab2
9* where mod(tab1.rn, 2) = 1 and tab2.rn = tab1.rn+1
SQL> /
ENAME ENAME
---------- ----------
ADAMS ALLEN
BLAKE CLARK
FORD JAMES
JONES KING
MARTIN MILLER
SCOTT SMITH
TURNER WARD
7 rows selected.
SQL> -- Yahoooo!
SQL> -- But what is that awful snippet of the query?
SQL> -- May be we should use another Oracle feature?
SQL> -- Analytics for example?
SQL> with source_table as
2 (select row_number() over (order by ename) rn
3 ,ename
4 from emp)
5 select tab1.ename, tab2.ename
6 from source_table tab1, source_table tab2
7 where mod(tab1.rn, 2) = 1 and tab2.rn = tab1.rn + 1
8 ;
ENAME ENAME
---------- ----------
ADAMS ALLEN
BLAKE CLARK
FORD JAMES
JONES KING
MARTIN MILLER
SCOTT SMITH
TURNER WARD
7 rows selected.
SQL> -- Gotcha :-)
SQL> -- But hmm... Maybe there is another way?
SQL> -- Certainly! Not just another.
SQL>
SQL>select max(decode(mod(rn, 2), 1, ename, NULL))
2 ,max(decode(mod(rn, 2), 0, ename, NULL))
3 from (
4 select row_number() over (order by ename) rn
5 ,ename
6 from emp
7 )
8 group by round(rn/2)
9* order by round(rn/2)
SQL> /
MAX(DECODE MAX(DECODE
---------- ----------
ADAMS ALLEN
BLAKE CLARK
FORD JAMES
JONES KING
MARTIN MILLER
SCOTT SMITH
TURNER WARD
7 rows selected.
That is all. Thanks a lot for your attention.
No comments:
Post a Comment