Some Queries About CTE

For selecting dept wise total number of employees

SELECT DEPTNO,COUNT(*) as no_of_employees FROM EMP GROUP BY DEPTNO

For selecting deptname and total number of employees in a dept

;WITH CTE AS (SELECT DEPTNO,COUNT(1)EMPCOUNT FROM EMP GROUP BY DEPTNO)
SELECT D.DNAME,D.DEPTNO,C.EMPCOUNT FROM DEPT D INNER JOIN CTE C ON C.DEPTNO=D.DEPTNO

Selecting hierarchy

;WITH A AS (SELECT * FROM EMP WHERE mgr is null
UNION ALL
SELECT E.* FROM emp e INNER JOIN A ON E.mgr=A.empno
)select * from A

For selecting 2 highest salary

SELECT TOP 1 SAL FROM (SELECT TOP 2 sal from emp ORDER BY SAL asc ) E ORDER BY SAL desc

Selecting top2 salaries

select top 2 sal from emp order by sal asc

Selecting top 1 sal from each dept

;WITH D AS(SELECT *,ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ) RNO FROM EMP)
SELECT * FROM D WHERE RNO=1

Selecting 5,6 highest sal 

;with x as (select rank()over (order by sal desc) salary ,* from emp)
select * from x where salary in (5,6)