Re: ORDER BY with exception

On Jun 21, 2007, at 8:08 PM, brian wrote:

> Michael Glaesemann wrote:
>> On Jun 21, 2007, at 17:35 , brian wrote:
>>> I have a lookup table with a bunch of disciplines:
>> To answer your ordering question first:
>> SELECT id, name
>> FROM discipline
>> ORDER BY name = 'other'
>>     , name;
>> id |        name
>> ----+---------------------
>>   8 | community
>>   4 | dance
>>   5 | film and television
>>   9 | fine craft
>>   7 | media arts
>>   3 | music
>>   6 | theatre
>>   2 | visual arts
>>   1 | writing
>> 10 | other
>> (10 rows)
>> This relies on the fact that FALSE orders before TRUE. I don't
>> always  remember which way, so I often have to rewrite it using <>
>> or = to  get the behavior I want.
>
> Of course! (slaps forehead)
>
>> I don't think you really need to use a function for this. I
>> believe  you should be able to do this all in one SQL statement,
>> something  like (if I've understood your query and intent correctly):
>> SELECT discipline.name, COUNT(showcase_id) AS total
>> FROM discipline
>> LEFT JOIN (
>>     SELECT DISTINCT discipline_id, showcase.id as showcase_id
>>     FROM showcase
>>     JOIN showcase_item on (showcase.id = showcase_id)
>>     WHERE accepted) AS accepted_showcases
>>         ON (discipline.id = discipline_id)
>> GROUP BY discipline.name
>> ORDER BY discipline.name = 'other'
>>     , discipline.name;
>>         name         | total
>> ---------------------+-------
>> community           |     0
>> dance               |     0
>> film and television |     0
>> fine craft          |     0
>> media arts          |     0
>> music               |     0
>> theatre             |     0
>> visual arts         |     1
>> writing             |     2
>> other               |     0
>> (10 rows)
>
> That's bang on, Michael, thanks a bunch. I never remember to
> explore joining on a select. I'm forever thinking in terms of
> joining on a table. Things to study this evening.

One way to break yourself of that habit is to stop thinking in terms
of tables when you query and replace that thinking with relations.
Queries are made on relations and tables are only one kind of
relation.  Then just remember that the results of select queries are
relations representing relationships between data in other relations
so they can themselves be used in select queries (as well as updates,
deletes and, as of 8.2, insert  and copy statements).

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com