Consider the following scenario: the business analyst of your company sends you a business requirement in which the text results of a given query must be ordered arbitrarily according to the specifics of that business requirement. For example: Us should precede Cs, which should precede Uns, which should precede Bs, which should precede everything else. It can be very costly to resort to manipulations of the result set after the query has been executed, while a stored procedure is not very portable. You can make the query itself return the results in the arbitrary order you wish by modifying the select statement in such a way that your arbitrary order will output a number (or any other orderable output) that can be used as an order by condition. An example of such technique is shown below (for PostgreSQL):


SELECT column_name
    CASE
        WHEN column_name LIKE pattern_1 THEN 1
        WHEN column_name LIKE pattern_2 THEN 2
        WHEN column_name LIKE pattern_3 THEN 3
        ELSE 4
    END AS arbitrary_order
FROM table_name
ORDER BY arbitrary_order

I will test this ordering with the ip2nation‘s table “ip2nationCountries”. Let’s assume the requirement says that the order should be based on the given list of prefixes: U, C, Un, B, remainder. Besides this order, all results should be returned in ascending order. At first, one can try the following query to fulfill this requirement:


SELECT country,
    CASE
        WHEN country LIKE 'U%' THEN 1
        WHEN country LIKE 'C%' THEN 2
        WHEN country LIKE 'Un%' THEN 3
        WHEN country LIKE 'B%' THEN 4
        ELSE 5
    END AS arbitrary_order
FROM "ip2nationCountries"
ORDER BY arbitrary_order, country

The result of this query is here. As you can see from the results, one problem is visible: “United States” appears before “China”, which does not fulfill the given requirements. This occurs because “United States” matches the first pattern in the case-switch (U%). This can be solved by placing the Un% case before U%:


SELECT country,
    CASE
        WHEN country LIKE 'Un%' THEN 3
        WHEN country LIKE 'U%' THEN 1
        WHEN country LIKE 'C%' THEN 2
        WHEN country LIKE 'B%' THEN 4
        ELSE 5
    END AS arbitrary_order
FROM "ip2nationCountries"
ORDER BY arbitrary_order, country

The result of this query is here.

This type of arbitrary ordering is very useful when you need to fill combos whose first results should reflect the interests of a particular group of customers, or when you need to decrease the amount of post-processing while creating a report.

This entry was posted by on Saturday, October 4th, 2008 at 10:42 pm and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply