Re: help with query: advanced ORDER BY...

On Sat, Jan 14, 2006 at 05:47:41PM +0100, me@alternize.com wrote:
> when selecting records from this table, i would like to have different sort
> orders depending if field1 is true or false: all records with field1 = true
> should be sorted with field2 first then field3, all records having field2 =
> false sorted by field3 first then field2.

Something like this might work:

SELECT * FROM table1
ORDER BY field1,
         CASE WHEN field1 THEN field2 ELSE NULL END,
         CASE WHEN field1 THEN field3 ELSE NULL END,
         CASE WHEN field1 THEN NULL ELSE field3 END,
         CASE WHEN field1 THEN NULL ELSE field2 END;

If field2 and field3 were the same type then you could shorten the
query to:

SELECT * FROM table1
ORDER BY field1,
         CASE WHEN field1 THEN field2 ELSE field3 END,
         CASE WHEN field1 THEN field3 ELSE field2 END;

The extra CASE statements in the first form are necessary if field2
and field3 are different types; with the shorter version you'd get
an error like:

ERROR:  CASE types character varying and numeric cannot be matched

--
Michael Fuhr