Sqlcookbook Learning Notes Results sort

Source: Internet
Author: User
Tags translate function



Select name from emp order by salary;
Order By default is sorted in ascending order, with ORDRE by salary DESC when reverse is required


Order by does not have to be based on the column name, or it can be expressed numerically based on the column:
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;
According to the Department ascending, within the department in descending order of wages.


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


Sort the mixed alphanumeric numbers
MySQL does not support the translate function, there is no solution.


Processing a sort null value
Use case to convert a null value to 0 or 1 to control the position of the null value in the sort.


Select Ename,sal,comm
From (
Select Ename,sal,comm,
Case when Comm was 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 ordering of NULL.


The restrained view is an intermediate result, not output.


Sorts according to the key of the data item.
If the job is Salasman it is sorted according to Comm, otherwise it is sorted by Sal.
Using a case expression in an ORDER BY clause
Select Ename,sal,job,comm
From EMP
Order BY case-job = ' salesman ' then comm else Sal End
Similarly, there is a secondary column.

Sqlcookbook Learning Notes Results sort

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.