ORDER OF DECLARATION IN SQL SERVER

Tables

ORDER OF DECLARATION

(USER FOLLOWS THIS ONE)

MEANS WHICH CLAUSE

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

ORDER OF EXECUTION (SYSTEM FOLLOWS)

FROM

WHERE

GROUP BY

HAVING

SELECT

Display employee records earning more than 2000

select * from emp where sal >2000

Display employee records earning more than 2000 and less than 5000

select * from emp where sal>2000 and sal<5000

Order by Clause

It is used to sort rows retrieved by the “select statement” in Ascending Order or Descending Order.

Syntax

Select */<collist>  from <tablename> [where condition] order by <colname> [asc,desc],

<colname> [asc,desc]

Default is ascending order

Display the details of employee in the order of highest salary to the lowest salary

select * from emp order by sal desc

     

Display the details of employee working in dept 30 in alphabetical order

select * from emp where deptno=30 order by ename

Display the details of employees in order of highest salary to lowest salary and arrange the employee who are drawing same salary.

select * from emp order by sal desc , ename asc

with in order by clause you can specify multiple columns when you specify the columns in order by then first it will arrange the rows in the specified order of first column and then only the rows that have same value in that first column will be arranged in the specified order of the second column.

Sorting the emp table in ename wise

Select * from emp order by ename

Or 

select * from emp order by 2

Arrange employee records department wise with in the department salary wise

Select deptno,ename,sal,job from emp order by deptno, sal desc

Display the employee records for 10 or 20 deptno and sort the result salary-wise in descending order

Select * from emp where deptno in(10,20) order by sal desc