1. Sort by single field

Ascending order: order by ASC (default)

Descending order: ORDER BY DESC

Query results are returned in the specified order. When actually extracting data or generating reports, they are generally viewed in a certain order. For example, you want to view the information of employees employed by the company.

SELECT empno, ename, hiredate FROM emp WHERE deptno = 10 ORDER BY hiredate ASC;

Many people can write the statement of sorting query results, but in addition to the expression of "order by hierarchical ASC", it can also be written as "ORDER BY 3 ASC", which means sorting by the third column.

SELECT empno, ename, hiredate FROM emp WHERE deptno = 10 ORDER BY 3 ASC;

When the value is not fixed, this method is very convenient. It should be noted that,

Replacing column positions with data can only be used in the order by clause, and can't be used anywhere else

2. Sort by multiple fields

What if you sort by multiple columns and there are ups and downs? For example, when sorting by department number in ascending order and by salary in descending order, there are two keywords: ASC indicates ascending order and DESC indicates descending order, so we add two columns after order by and mark ASC and DESC respectively.

SELECT empno, deptno, sal, ename, job FROM emp ORDER BY 2 ASC, 3 DESC;

 

The following is introduced in the form of a diagram, as shown in the following figure. When sorting multiple columns, the following sorting is useful only if the previous column has duplicate values (for example, deptno = 10 has 3 rows of data). So the data is divided into several groups through the front column, and then the data of each group is sorted according to the rear column.

 3. Sort by substring

There is a quick query method for sorting by substring, which is to record according to the sequence of the tail number of the customer's telephone number, so that when searching, the query scope can be quickly reduced and the customer's recognition can be enhanced. If you want to sort in this way, what should you do? Take out the required information of the next few bits through the function.

SELECT last_name AS name,
       phone_number AS number,
       salary as wages,
       substr(phone_number, -4) AS last digits of a number
  FROM hr.employees
 WHERE rownum < 5
 ORDER BY 4;

It can be seen that as long as the data can be queried, it can be sorted according to the corresponding information.

4. TRANSLATE

Syntax format: TRANSLATE(expr, from_string, to_string)

Examples are as follows:

SELECT TRANSLATE( 'ab Hello bcadefg', 'abcdefg', '1234567' ) AS NEW_STR FROM DUAL;

 

from_string and to_string is in characters, and the corresponding characters are replaced one by one.

If to_ If string is null, a null value is returned

SELECT TRANSLATE( 'ab Hello bcadefg', 'abcdefg', '' ) AS NEW_STR FROM DUAL;

If to_ There is no character in the position corresponding to string. Delete from_ The characters listed in string will be eliminated.  

SELECT TRANSLATE( 'ab Hello bcadefg', '1abcdefg', '1' ) AS NEW_STR FROM DUAL;

 

 

5. Sort by letters in a mixed string of numbers and letters

First create the VIEW as follows:

CREATE OR REPLACE VIEW V 
as 
SELECT empno || ' ' || ename AS data FROM emp;

select * from V;

This requirement is a little more difficult. Do you see the letters inside (that is, the original column ename)? It is required to sort by the letter (column ename).

Then we need to take out the letters first. We can use the replace function of translate to replace the numbers and spaces with empty ones:

SELECt data, translate (data, '- 0123456789', '-' )AS ename
  FROM v
 ORDER BY 2;

 

6. Process sorting null values

The default sorting null value of Oracle is in the back. What if you want to display the null value (such as emp.comm) in the front? Use NVL(comm, - 1)?

SELECT ename, sal, comm, nvl(comm， - 1) order_col FROM emp ORDER BY 4;

 

Maybe many people use this method, but this method needs to understand the column type and the data saved in it, and if the saved data changes, the statement needs to be maintained again.
In fact, you can use the keywords NULLS FIRST and NULLS LAST.

Null value first

SELECT ename, sal, comm FROM emp ORDER BY 3 NULLS FIRST;

Null value after

SELECT ename, sal, comm FROM emp ORDER BY 3 NULLS LAST;

Is it much more convenient?

7. Sort by taking values from different columns according to conditions

Sometimes the sorting requirements are complex. For example, leaders are more interested in employees with wages between 1000 and 2000 yuan, so they require employees with wages in this range to be ranked first for priority inspection.
For this requirement, we can generate a new column in the query and sort it by multiple columns:

SELECT empno AS code,
       ename AS full name,
       CASE
         WHEN sal > 1000 AND sal < 2000 THEN
          1
         ELSE
          2
       END AS level,
       sal AS wages
  FROM emp
 WHERE deptno = 30
 ORDER BY 3, 4;

 

 

As you can see, 950 and 2850 are ranked behind, or you can directly put case when in order by without displaying the level

SELECT empno AS code, ename AS full name, sal AS wages
  FROM emp
 WHERE deptno = 30
 ORDER BY CASE
            WHEN sal >= 1000 AND sal < 2000 THEN
             1
            ElSE
             2
          END,
          3;