Skip site navigation (1) Skip section navigation (2)

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

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: me(at)alternize(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: help with query: advanced ORDER BY...
Date: 2006-01-14 17:18:29
Message-ID: 20060114171829.GA64741@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
On Sat, Jan 14, 2006 at 05:47:41PM +0100, me(at)alternize(dot)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

In response to

Responses

pgsql-novice by date

Next:From: meDate: 2006-01-14 20:32:03
Subject: Re: help with query: advanced ORDER BY...
Previous:From: meDate: 2006-01-14 16:47:41
Subject: help with query: advanced ORDER BY...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group