Here are some notes I’ve been making from the book SQL Cookbook Query Solutions and Techniques for All SQL Users (Anthony Molinaro, Robert de Graaf), specifically from chapter 2.

Chapter 2

Ordering can be done by multiple columns using ORDER BY, adding DESC after each column name if in descending order. Example: Get all employees, ordered according to salary in descending order, and in alphabetical order of their names. SELECT * FROM employee ORDER BY salary DESC, name; You can order by columns not currently selected, as long as you’re not using clauses DISTINCT or GROUP BY, in which case only columns selected can be used.

Sorting by substring can be done by using the SUBSTR function in the ORDER BY clause. Example: Get all rows from employee ordered in ascending order by the last two characters in their name. SELECT * FROM employee ORDER BY SUBSTR(name,length(name)-1);

The TRANSLATE() function is used to replace a set of characters in a string with another set of characters. It takes three arguments: input_string, from_characters and to_characters. Eg: SELECT TRANSLATE(‘Hello World’, ‘oW’, ‘ax’); This returns Hellax axrld

The REPLACE() function replaces all occurrences of a substring within a string with a new substring. The search is case insensitive.

Together these can be used to order strings that contain numbers and characters. Example: Given a view v with column data name deptno, ordering by name or deptno can be done by removing one or the other, and using the result to order.

To order by deptno: SELECT DATA FROM V ORDER BY REPLACE(DATA, REPLACE( TRANSLATE(data, ‘0123456789’,’##########’),’#’,”),”)  This removes the numbers, then removes the remainder from data.

similarly for ordering by name: SELECT DATA FROM V ORDER BY REPLACE(DATA,TRANSLATE(data, ‘0123456789’, ’#########’), ’#’, ”)

Suppose we want to order by a nullable column, and then with other columns, then using CASE WHEN AND END to create a separate column where null is represented as an integer (0 or 1) and non null as (1 or 0) and then order by that. for instance:  order by commission name, salary and commission with null values being separate in employee. SELECT name, salary, commission fom (SELECT name, salary, commission, CASE WHEN commission IS NULL THEN 0 ELSE 1 END AS is_null from employee) AS temp ORDER BY is_null desc, commission desc;

Suppose we want to order employees, by commission if they are salespeople, but by salary otherwise, then we can use a CASE THEN ELSE END clause. SELECT * FROM employee ORDER BY CASE WHEN job = ‘salesman’ THEN commission ELSE salary END