Received: from s-nath-exch2.nath-ctmp.co.za ([209.212.102.30]) by hub.org (8.9.3/8.9.3) with ESMTP id GAA17507; Fri, 16 Jul 1999 06:04:31 -0400 (EDT) (envelope-from Michael.Ansley@intec.co.za) Received: by S-NATH-EXCH2 with Internet Mail Service (5.5.2448.0) id <35KRP9SJ>; Fri, 16 Jul 1999 12:01:29 +0200 Message-ID: <1BF7C7482189D211B03F00805F8527F70ED04A@S-NATH-EXCH2> From: "Ansley, Michael" To: "Ansley, Michael" , "'Michael Richards'" Cc: pgsql-sql@postgreSQL.org, hackers@postgreSQL.org, Tom Lane Subject: RE: [HACKERS] Counting bool flags in a complex query Date: Fri, 16 Jul 1999 12:01:26 +0200 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain; charset="iso-8859-1" Sorry guys, this line: >> ORDER BY (if(folderid < 0) then return(abs(folderid)) else >> return(folderid+max(abs(MIN(folderid))))) Should have read: ORDER BY (if(folderid < 0) then return(abs(folderid)) else return(folderid+abs(MIN(folderid)))) The max was an error. MikeA >> >> Why don't you adjust the ids of your system folders, such >> that they are >> ordered properly? You should have a fixed number of system >> folders, so you >> can guarantee the ids that they will receive. So make the >> Inbox -4. Then >> you just order by folder id, ascending. -4 comes first, >> with the user >> folders always coming after the system folders. >> >> Alternatively, you can sort by an expression, something like: >> >> ORDER BY (if(folderid < 0) then return(abs(folderid)) else >> return(folderid+max(abs(MIN(folderid))))) >> >> What this does is shift all the ids up to ensure that they >> all fall into the >> positive range, while inverting the order of the negative >> ids, which seems