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