WALKUP TREES AND INLINE
VIEW
WALKUP TREES
Using hierarchical
queries, you can retrieve data based on a natural hierarchical relationship
between rows in a table. However, where a hierarchical relationship exists
between the rows of a table, a process called tree walking enables the
hierarchy to be constructed.
Ex:
SQL> select ename || '==>'
|| prior ename, level from emp start
with ename = 'KING'
connect by prior
empno=mgr;
ENAME||'==>'||PRIORENAM LEVEL
------------------------------------ --------
KING==> 1
JONES==>KING 2
SCOTT==>JONES 3
ADAMS==>SCOTT 4
FORD==>JONES 3
SMITH==>FORD 4
BLAKE==>KING 2
ALLEN==>BLAKE 3
WARD==>BLAKE 3
MARTIN==>BLAKE 3
TURNER==>BLAKE 3
JAMES==>BLAKE 3
CLARK==>KING 2
MILLER==>CLARK 3
In the above
Start with clause specifies the root row of the table.
Level pseudo column gives the 1 for root , 2 for child and so on.
Connect by prior clause specifies the columns which has
parent-child relationship.
INLINE VIEW OR TOP-N ANALYSIS
In the select statement instead of table name, replacing the
select statement is known as inline view.
Ex:
SQL> Select ename, sal, rownum
rank from (select *from emp order by sal);
ENAME SAL RANK
---------- ---------- ----------
SMITH 800 1
JAMES 950 2
ADAMS 1100 3
WARD 1250 4
MARTIN 1250 5
MILLER 1300 6
TURNER 1500 7
ALLEN 1600 8
CLARK 2450 9
BLAKE 2850 10
JONES 2975 11
SCOTT 3000 12
FORD 3000 13
KING 5000 14
Q) How to Nth Highest sal from a table?
Ans:
SQL> Select * from(Select ename,sal,dense_rank() over(order by
sal desc) nth_hisal from emp)Where nth_hisal=&eval;
No comments:
Post a Comment