Oracle Series: (7) ORDER BY clause

Query Employee information (number, name, salary, annual salary), sorted by monthly salary, ascending by default, sorted by Oracle built-in check rule if monthly salary is the same

Select empno,ename,sal,sal*12 from emp order by SAL ASC;

Query Employee information (number, name, salary, annual salary), in descending order by monthly salary

Select empno,ename,sal,sal*12 from emp order by Sal Desc;

Query employee information, sorted in descending order by entry date, using column names

Select empno,ename,sal,hiredate,sal*12 "Annual Salary" from Emporder by hiredate Desc;

The order by can follow the column name, alias, expression, column number (starting with 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;

An expression:

Select empno,ename,sal,hiredate,sal*12 "Annual Salary" from Emporder by sal*12 Desc;

Column number, starting from 1:

Select empno,ename,sal,hiredate,sal*12 "Annual Salary" from Emporder by 5 desc;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/86/EF/wKioL1fPF0bQLpX5AABCAPF3IRc916.png "title=" 009. PNG "alt=" Wkiol1fpf0bqlpx5aabcapf3irc916.png "/>

Query employee information, sorted by commission in ascending or descending order,null value as maximum value

SELECT * FROM emp ORDER BY comm Desc;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/86/F0/wKiom1fPF6DgSemrAABLe8uXUic010.png "title=" 001. PNG "alt=" Wkiom1fpf6dgsemraable8uxuic010.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/86/F0/wKiom1fPGADDzF0EAABLxyovWcg229.png "title=" 002. PNG "alt=" Wkiom1fpgaddzf0eaablxyovwcg229.png "/>

Query employee information, in descending order of commissions for employees with commissions, when order by and where are present, order by at the end

Select *from Empwhere comm is not nullorder by comm Desc;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/86/F0/wKiom1fPGErhIamVAAAioTXH5yA219.png "title=" 003. PNG "alt=" Wkiom1fpgerhiamvaaaiotxh5ya219.png "/>

Query employee information in descending order of wages, and employees of the same wage are then sorted in descending order of entry time

Select *from emporder by Sal Desc,hiredate desc;

Select *from Emporder by Sal Desc,hiredate ASC;

Note: The HireDate sort is only useful if Sal is the same

Query number 20th, with a salary greater than 1500, sorted in descending order of the entry time

Select *from Empwhere (deptno=20) and (sal>1500) Order by hiredate Desc;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/86/F0/wKiom1fPGQmAzXylAAAeeNuz97c501.png "title=" 004. PNG "alt=" Wkiom1fpgqmazxylaaaeenuz97c501.png "/>

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

SELECT * from EMP where deptno in (10,20,30,50, ' 30 ');

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/86/EF/wKioL1fPGmvQxzGfAABDHNXFTQk025.png "title=" 005. PNG "alt=" Wkiol1fpgmvqxzgfaabdhnxftqk025.png "/>

SELECT * from EMP where deptno in (10,20,30,50, ' a ');

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/86/EF/wKioL1fPGnjzZi9iAAAOyYvTm94432.png "title=" 006. PNG "alt=" Wkiol1fpgnjzzi9iaaaoyyvtm94432.png "/>

Oracle Series: (7) ORDER BY clause