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
