Sorting by Using the ORDER BY Clause

Topic Introduction: In this tutorial, we will discuss Sorting by Using the ORDER BY Clause. Sorting means organizing rows serially in which data is retrieved from a query. The ORDER BY clause will be used to sort the rows.

Rules of Sorting

• To sort the retrieved rows we need to use the ORDER BY clause
• ASC: Ascending order, default
• DESC: Descending order
• Numeric values are displayed with the lowest values first (for example, 0 to 999)
• Date values are displayed with the earliest value first (for example, 01-JAN-23 before 01-JAN-30).
• Character values are displayed in alphabetical order (for example, “A” first and “Z” last).
• Null values are displayed last for ascending sequences and first for descending sequences.
• We can use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows
  containing null values should appear first or last in the ordering sequence.
• We can also sort by a column that is not in the SELECT list.
• We can specify an expression, an alias, or a column position as the sort condition.
• The ORDER BY must be the last clause of the SQL statement 

Example 1: Here we use a database column and its will return row will be sorted by lowest to highest by joining date as here do not use ASC or DESC by default ASC. 

select employee_id,first_name||' '||last_name employee_name,hire_date,salary, salary*12 annual_salary

from employees

order by hire_date;

Example 2: Here we use a database column and its will return row will be sorted by highest to lowest by joining date here we use the DESC keyword

select employee_id,first_name||' '||last_name employee_name,hire_date,salary, salary*12 annual_salary

from employees

order by hire_date desc;

Example 3: Here we use Column alias instant of a database column and its will return row will be sorted by lowest to the highest alphabetic order of employee names as here do not use ASC or DESC by default ASC.

select employee_id,first_name||' '||last_name employee_name,hire_date,salary, salary*12 annual_salary

from employees

order by employee_name;

Example 4: Here department_id does not exist on select clause but uses department_id  on order by clauses that are why the return row will be sorted by department_id. and 2nd sort by salary.

select employee_id,first_name||' '||last_name employee_name,hire_date,salary, salary*12 annual_salary

from employees

order by department_id,salary;

Example 5: Here row will be short by commission_pct  and for using NULLS FIRST null value will show first.

select employee_id,first_name||' '||last_name employee_name,hire_date,salary, salary*12 annual_salary

from employees

order by commission_pct nulls first;

Example 6: Here data will be returned by salary because the salary column position on 4.

select employee_id,first_name||' '||last_name employee_name,hire_date,salary, salary*12 annual_salary

from employees

order by 4;

.