Derived tables :-
- This is introduced from SQL Server 2005.
- Sub queries follows from clause are called “Derived tables”.
Syntax :
Select * from (select statement) as alias.
- Use derived tables to simplify the complex processing.
- To use result of one query in another query.
Example :-
Select * from (select empno,ename,sal*12 annsal from emp) as e where annsal >2000.
Display top maximum 3 salaries in emp table :-
Select distinct sal from (select ename,sal, dense_rank ( ) over (order by sal desc) rnk from emp) as e where rnk <=3
{to remove this one, this is top min 3 sal}
Display department wise top maximum 3 salaries in emp table :-
Select distinct deptno,sal from (select ename,sal,deptno,dense_rnk ( ) over (partition by dept no order by sal desc rnk from emp) as e Where rnk c=3 order by deptno,sal desc
sales 1, sales 2, cust →
Select * from (select * from sales 1 union all select * from sales 2) as s, cust c where s.cusid=c.cusid