pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ansley, Michael <Michael.Ansley@intec.co.za>
To: 'Michael Richards' <miker@scifair.acadiau.ca>
Cc: pgsql-sql@postgreSQL.org, hackers@postgreSQL.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: RE: [HACKERS] Counting bool flags in a complex query
Date: Fri, 16 Jul 1999 11:18:40 +0200
Message-ID: <1BF7C7482189D211B03F00805F8527F70ED049@S-NATH-EXCH2> (raw)

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
like it's what you want.  Of course, this isn't legal SQL.  You would
probably have to write a function to implement this.  This will work no
matter what folders you add, system or user, and will always give you the
oldest folders (i.e.: those with the lowest absolute id) first, for each
group.
The MAX will make it slow though, except, of course, that in a function, you
can store the value, instead of repeatedly looking it up.

So:

SELECT 	folderid,
		foldername,
		count(*) as "messgaes",
		sum(bool2int(flagnew)) as "newmessages",
		sum(contentlength) as "size" 
FROM 		usermail,folders 
WHERE 	usermail.loginid='michael' AND
		folders.loginid=usermail.loginid AND
		usermail.folder = folders.folderid 
GROUP BY 	folderid,foldername 

UNION ALL

SELECT 	folderid,
		foldername,
		0,
		0,
		0 
FROM 		folders 
WHERE 	loginid='michael' AND
		NOT EXISTS (SELECT folder 
				FROM usermail 
				WHERE loginid='michael' AND
					folder=folderid
				) 

ORDER BY get_effective_order(folderid);

And then define the function get_effective_order using pgsql to return the
value described above.


However, I don't think that you are going to get away from the UNION ALL.

BTW
If you are going to do this:
>> fastmail=> select 1 as "test" order by (test<9);
then why not just do this:
select 1 as "test" order by (1<9);
If you actually have a field, then you would be able to put it in.  If you
have an expression like this:
select x+y\z as "some_number" from test order by (somenumber>9);
then you could just as easily do this:
select x+y\z as "some_number" from test order by (x+y\z>9);
That's why the expression will not evaluate properly, I think.

MikeA

>> 
>> My folder numbers are: negative numbers are system folders 
>> such as New
>> mail, trash, drafts and sentmail. I wanted to order the 
>> tuples so that the
>> folderids were sorted from -1 to -4, then 1 to x. This way the system
>> folders would always appear first in the list.

<big snip>

>> Using a column name within an expression in the order by 
>> does not seem to
>> work...
>> Or a much simpler example to illustrate the bug:
>> fastmail=> select 1 as "test" order by (test<9);
>> ERROR:  attribute 'test' not found
>> 
>> fastmail=> select 1 as "test" order by test;
>> test
>> ----
>>    1
>> (1 row)
>> 
>> 

<not so big snip>

>> 
>> Do I need outer joins to make this work instead of the 
>> screwed up union
>> method I'm trying here, or is it just a series of bugs?
>> 
>> -Michael
>> 
>> 



view thread (12+ messages)  latest in thread

Message-ID: <1BF7C7482189D211B03F00805F8527F70ED049@S-NATH-EXCH2>
Permalink:  ../1BF7C7482189D211B03F00805F8527F70ED049@S-NATH-EXCH2/
Also on:    postgresql.org/message-id/1BF7C7482189D211B03F00805F8527F70ED049@S-NATH-EXCH2

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: Michael.Ansley@intec.co.za, miker@scifair.acadiau.ca, tgl@sss.pgh.pa.us
  Subject: Re: [HACKERS] Counting bool flags in a complex query
  In-Reply-To: <1BF7C7482189D211B03F00805F8527F70ED049@S-NATH-EXCH2>
  References: <1BF7C7482189D211B03F00805F8527F70ED049@S-NATH-EXCH2>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox