Re: ORDER BY with exception

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.

> As a general rule, it's generally better to let the server handle the
> data in sets (i.e., tables) as much as possible rather than using
> procedural code.
>
> Hope this helps.

It helped lots, thanks again.

brian