David Turover

• Arbitrary Sorting
• Optimizing O(n^2) SQL queries

Arbitrary sorting in SQL

Sometimes you need to sort in a different manner than SQL's ORDER BY command. Fortunately, there's a hack to do this.

SQL allows for boolean (true/false) values to be interpreted as integers (whole numbers). According to SQL92, the value of "false" is 0 and the value of "true" is 1. In your SELECT statement, you can create a calculated field whose value is the result of a true/false comparison against the other fields, massaged to your liking with additional artithmetic.

Unfortunately, you cannot normally ORDER BY a calculated field because you can't ORDER BY an alias. This generally makes ordering by a calculated field impossible unless you can ORDER BY the exact calculation, but there's a hack around that too!

You can order by a field's alias in a UNION query. To change your normal query into a UNION query, you will have to split it into two queries. The easiest way is to have one SELECT for a known minimum or maximum value of the field being compared (or just SELECT where 1 = 0), and let the other SELECT choose everything else.

Example:

SELECT foo,
 (foo = 1) AS bar
FROM my_table
WHERE (foo = 1)
UNION ALL
SELECT foo, (
 (foo = 2) * 4 +
 (foo = 3) * 2 +
 (foo = 4) * 3
) AS bar
FROM my_table
WHERE (foo <> 1)
ORDER BY bar;

Alternate example

SELECT foo, 0 AS bar
FROM my_table
WHERE (0 = 1)
UNION ALL
SELECT foo, (
 (foo = 1) * 1 +
 (foo = 2) * 4 +
 (foo = 3) * 2 +
 (foo = 4) * 3
) AS bar
FROM my_table
ORDER BY bar;

This example would sort the field "foo" in the order 1,3,4,2 (or reverse that in MSAccess). You can add any number of such sorted fields as you need them.

Exceptions:

• In Microsoft Access, true is -1, not the standard 1. False is still 0.
• PostgreSQL (7.3.2 as of this writing) is not capable of interpreting booleans as integers.
• MySQL versions before 4.0 do not support UNION queries.

---

Optimizing SQL Queries

SQL queries which include a subquery in the WHERE clause are terribly inefficient. The entire subquery is re-run for each record in the table, leading to O(n^2) inefficiency.

Pseudocode:
for each record {
    re-run entire subquery
}

You can speed up these types of queries by moving the subquery into the SELECT or FROM clause.

 -- Before:
SELECT foo FROM my_table AS A
WHERE (SELECT COUNT(foo) FROM my_table AS B WHERE A.foo = B.foo) = 1;

-- After:
SELECT A.foo
FROM my_table AS A,
(SELECT foo, COUNT(foo) as amt FROM my_table GROUP BY foo) as B
WHERE A.foo = B.foo
and B.amt = 1;

With subselect into the FROM clause, the subselect query only needs to be run once.