MS-SQL 정렬문법 [order by , top ]

-- order by 오름차순 desc입력시 내림차순.
select ename, sal
from emp
order by sal

select job, avg(sal) as [avgsal]
from EMP
group by JOB
order by avgsal desc

--top 상위 5개만 보여줌.
select top 5 ename, SAL
from emp
order by SAL desc

select top 30 percent ename, SAL
from EMP
order by SAL desc

--with ties 동률. 같은값이 있는경우에는 같이 표시.
select top 2 with ties ename, SAL
from EMP
order by sal desc

--group by 이후 처리가 필요할경우 having으로 처리하여줌.
select job , AVG(sal) as [평균연봉]
from emp
where job != 'clerk'
group by job
having avg(SAL) >= 3000
order by avg(sal)

--with rollup 시 이전 값에대한 전체값을 리턴.
select job, deptno, avg(sal)
from emp
group by deptno,job with rollup

select deptno, job, sal
from emp
order by DEPTNO desc
compute avg(sal) by deptno