Oracle series: (7) order by clause

Query employee information (number, name, monthly salary, annual salary), sort by monthly salary ascending order, default ascending order, if monthly salary is the same, sort by oracle built-in verification rules

Select empno,ename,sal,sal*12 from emp order by sal asc

Query employee information (number, name, monthly salary, annual salary), sorted by monthly salary descending order

Select empno,ename,sal,sal*12 from emp order by sal desc

Query employee information, sort by entry date, and use column names

Select empno,ename,sal,hiredate,sal*12 "annual salary" from emporder by hiredate desc

Order by can be followed by column name, alias, expression, column number (starting from 1, column number in the select clause)

Column name:

Select empno,ename,sal,hiredate,sal*12 "annual salary" from emporder by hiredate desc

Alias:

Select empno,ename,sal,hiredate,sal*12 "annual salary" from emporder by "annual salary" desc

Expression:

Select empno,ename,sal,hiredate,sal*12 "annual salary" from emporder by sal*12 desc

Column number, starting with 1:

Select empno,ename,sal,hiredate,sal*12 "annual salary" from emporder by 5 desc

Query employee information in ascending or descending order of commission, and the null value is regarded as the maximum

Select * from emp order by comm desc

Query employee information, for employees with commission, in descending order of commission. When order by and where appear at the same time, order by is at the end.

Select * from empwhere comm is not nullorder by comm desc

Query the employee information, arrange it in the descending order of wages, and then arrange the employees of the same salary in descending order of entry time.

Select * from emporder by sal desc,hiredate desc;select * from emporder by sal desc,hiredate asc

Note: hiredate sorting works only if the sal is the same

Query Department 20, and the salary is greater than 1500, arranged in descending order by entry time.

Select * from empwhere (deptno=20) and (sal > 1500) order by hiredate desc

The following string '30' can be implicitly converted to a number

Select * from emp where deptno in (10, 20, 30, 50, 50, 10, 10, 10, 20, 30, 50, 50, 10, 10, 10, 20, 50, 50, 50, 10, 10, 10, 50, 50, 50, 10, 10, 50, 50, 10, 10, 50, 50, 10, 10, 50, 50, 10, 10, 10, 50, 50, 10, 10, 50, 50, 10, 10, 10, 50, 50, 10, 10, 50, 50, 10, 10, 10, 50, 50, 10, 10, 10, 10, 50, 50, 10, 10, 10, 50, 50, 10, 10, 50, 50, 10, 10, 10, 50, 50, 10, 10, 10, 50, 50, 10, 10, 10, 50, 50, 10, 10, 10, 50, 50, 10, 10, 10,

Select * from emp where deptno in (10, 20, 30, 50, 50, 10, 10, 20, 50, 10, 10, 10, 20, 50, 50, 10, 10, 20, 50, 50, 10, 10, 10, 20, 50, 50, respectively).