SQLCookbook sorting of study notes and impression notes

SQLCookbook sorting of study notes and impression notes


Select name from emp order by salary;
BY default, order by is arranged in ascending ORDER. To do so in descending ORDER, use ordel BY salary DESC.


Order by does not have to be based on the column name, but can also be represented BY numbers based on the column number:
Select name from emp order by 3; salary is the third column from left to right.


Sort by multiple fields


Select * from emp order by depno, salary desc;
Sort by department in ascending order, and sort by salary in descending order within the Department.


Sort by string
Select * from emp order by substr (job, length (job)-2)
Sort by the last two characters of the work field.


Sorting of mixed letters and numbers
MySQL does not support the TRANSLATE function and has no solution.


Processing sorting null values
Use CASE to convert a null value to 0 or 1, which can control the position of the null value in the sorting.


Select ename, sal, comm
From (
Select ename, sal, comm,
Case when comm is null then 0 else 1 end as is_null
From emp
) X
Order by is_null, comm desc
The above method uses an additional column


ORACLE has nulls first and nulls last to ensure the NULL sorting method.


An restrained view is an intermediate result and is not output.


Sort data items by keys.
If the job is salasman, It is sorted by COMM; otherwise, it is sorted by sal.
Use the CASE expression in the order by clause
Select ename, sal, job, comm
From emp
Order by case when job = 'salesman' then comm else sal end
Similar to a secondary column.