MySQL ORDER BY clause

➔ If no ORDER BY clause is used, the order of the rows in the query results of a SELECT statement on a table is unspecified.

➔ When the ORDER BY clause is used, the order of rows in the query results of the SELECT statement is in ascending order by default.

➔ The ASC keyword sorts data in ascending order (A-Z, 0-9) and the DESC keyword sorts data in descending order (Z-A, 9-0). If no keyword is specified, MySQL uses ASC by default.

➔ To sort the result set by multiple columns, specify a comma-separated list of columns in the ORDER BY clause: (e.g. ORDER BY column1, column2).

MySQL ORDER BY syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

Example: Sort employees by their last_name in ASC order.

SELECT first_name, last_name, salary, job_id, department_id, hire_date
FROM employees 
ORDER BY last_name;

Example: Sort employees by salary in DESC order (highest to lowest).

SELECT first_name, last_name, salary, job_id, department_id, hire_date
FROM employees 
ORDER BY salary DESC;

Example: First, Sort employees by department (ascending), then by salary (descending) within that department.

SELECT first_name, last_name, department_id, salary, job_id, hire_date
FROM employees 
ORDER BY department_id ASC, salary DESC;

Instead of names, the numerical position of the column in the SELECT list can also be used to sort the data set. (1 for the first column, 2 for the second, etc).

Example: The query results are sorted by the second column (last_name)

SELECT first_name, last_name, department_id, salary, job_id
FROM employees 
ORDER BY 2;

MySQL allows sorting by calculated values ​​using expressions or column aliases defined in the SELECT clause.

Example: Sort employees using alias.

SELECT first_name, last_name, salary, (salary * 12) AS "Anual Salary" 
FROM employees
ORDER BY "Anual Salary" DESC;

Example: Sort employees using expression.

SELECT first_name, last_name, salary, (salary * 12) AS "Anual Salary", COALESCE(commission_pct, 0) * salary * 12 Commission 
FROM employees
ORDER BY COALESCE(commission_pct, 0) * salary * 12 DESC;

Example: Use the RAND() function to find employees in random order.

SELECT first_name, last_name, department_id, salary, job_id 
FROM employees 
ORDER BY RAND() 
LIMIT 5;

Example: Sort employees by salary (lowest to highest) and NULL values (if any) ​​will be displayed at the end

SELECT first_name, salary, department_id, job_id
FROM employees
ORDER BY salary IS NULL, salary ASC;

Example: Sort employees by salary (highest to lowest) and put NULL values (if any) ​​at the top.

SELECT first_name, salary, department_id, job_id
FROM employees
ORDER BY salary IS NULL, salary ASC;

The COALESCE() function can replace NULL with a specific value (such as 0).

Example: Find employee salaries including commission and sort the results by salary (highest to lowest)

SELECT first_name, salary, COALESCE(commission_pct, 0) AS "Comm", 
salary+salary*COALESCE(commission_pct, 0) AS "Salary+Comm",  job_id 
FROM employees
ORDER BY salary DESC;

Custom sort in ORDER BY

➔ Custom sorting is possible in the ORDER BY clause, which allows results to be sorted according to specific business logic rather than just simple alphabetical or numerical order.

➔ In MySQL, the FIELD() function or CASE expressions can be used to perform custom sorting.

➔ These methods allow for a specific order to be determined that does not follow the conventional alphabetical or numerical order.

Example: This SQL query retrieves first name, salary, department ID, and job ID from the employees table and sorts them in a custom, specific order based on their department_id.

SELECT first_name, salary, department_id, job_id
FROM employees
ORDER BY FIELD(department_id, '40', '50', '30');

Example: This SQL query retrieves employee information and sorts the results based on a custom, specific order based on their job_id.

SELECT first_name, salary, department_id, job_id 
FROM employees
ORDER BY FIELD(job_id, 'SA_REP', 'IT_PROG', 'MK_REP');

Example: This SQL query uses CASE expressions to retrieve employee information and sorts the results in a custom, non-alphabetical order based on their job_id.

SELECT first_name, job_id, salary, department_id 
FROM employees 
ORDER BY CASE 
    WHEN job_id = 'SA_REP' THEN 1
    WHEN job_id = 'HR_REP' THEN 2
    WHEN job_id = 'IT_PROG' THEN 3
    ELSE 4 
END ASC;

Note: For -1 it returns everything to the right of the delimiter and for 1 it returns everything to the left of the delimiter.

Example: Sort full_name by first_name using the SUBSTRING_INDEX() function.

SELECT CONCAT(first_name, '_', last_name) AS full_name 
FROM employees 
ORDER BY SUBSTRING_INDEX(full_name, '_', 1) ASC; 

Example: Sort full_name by last_name using the SUBSTRING_INDEX() function.

SELECT CONCAT(first_name, '_', last_name) AS full_name, job_id, salary
FROM employees 
ORDER BY SUBSTRING_INDEX(full_name, '_', -1) ASC; 

Sort the result set using the CHAR_LENGTH() function:
Example:

SELECT first_name, last_name, salary, job_id
FROM employees 
ORDER BY CHAR_LENGTH(last_name) DESC;