Re: ORDER BY with exception
От | Josh Tolley |
---|---|
Тема | Re: ORDER BY with exception |
Дата | |
Msg-id | e7e0a2570706211635y21533a7ckc3a981a77c08ec5e@mail.gmail.com обсуждение исходный текст |
Ответ на | ORDER BY with exception (brian <brian@zijn-digital.com>) |
Ответы |
Re: ORDER BY with exception
(brian <brian@zijn-digital.com>)
|
Список | pgsql-general |
On 6/21/07, brian <brian@zijn-digital.com> wrote: > I have a lookup table with a bunch of disciplines: > > # SELECT id, name FROM discipline; > id | name > ----+--------------------- > 1 | writing > 2 | visual arts > 3 | music > 4 | dance > 5 | film and television > 6 | theatre > 7 | media arts > 8 | community > 9 | fine craft > 10 | other > (10 rows) > > and a function that returns each discipline name along with the total > number of records in another table (showcase) that are related to each > discipline. Each showcase entry may have 0 or more items (showcase_item) > related to it, so ones that have no items are disregarded here. Also, > only showcases that have been accepted should be counted. > > First, here's the working function: > > CREATE FUNCTION getshowcasetotalsbydiscipline(OUT name text, OUT total > integer) RETURNS SETOF record > AS $$ > > DECLARE > rec record; > > BEGIN > FOR rec IN > EXECUTE 'SELECT id, name, 1 AS total FROM discipline' > LOOP > name := rec.name; > > SELECT INTO rec.total > > -- a showcase may be in the DB but not accepted by an admin > SUM(CASE s.accepted WHEN TRUE THEN 1 ELSE 0 END) > FROM showcase AS s > WHERE s.id IN > > -- a showcase may exist with no items, so should be ignored > (SELECT si.showcase_id FROM showcase_item AS si > WHERE si.discipline_id = rec.id); > > -- If no showcase items have this discipline, > -- give it a total of zero > > IF rec.total IS NULL THEN > SELECT INTO total 0; > ELSE > total := rec.total; > END IF; > > RETURN NEXT; > END LOOP; > > RETURN; > END; > $$ LANGUAGE plpgsql IMMUTABLE; > > test=# SELECT * FROM getShowcaseTotalsByDiscipline(); > name | total > ---------------------+------- > writing | 130 > visual arts | 252 > music | 458 > dance | 131 > film and television | 102 > theatre | 271 > media arts | 83 > community | 20 > fine craft | 78 > other | 59 > (10 rows) > > Works fine, but i'd like to order the disciplines alphabetically > *except* have 'other' fall at the end. So, should i loop a second time, > after summing the totals, and keep the 'other' row aside, then add it to > the end? > > (btw, the output of this function is cached until a new showcase is > accepted) > > Or, should i re-order the disciplines alphabetically in the lookup > trable, keeping 'other' to be last? > > I could do the latter, although it would mean a fair bit of work because > the disciplines table relates to a bunch of other stuff, as well. Also, > there's always the chance that a new discipline will be added in the > future. I suppose i could write a trigger that bumped the 'other' id > above that of the new entry, then re-relate everything else in the DB > that's connected to the 'other' discipline. But that strikes me as kind > of a hack. > > The third option is to re-order the resultset in the PHP script that > displays this. But that wasn't why i chose Postgres for this app ;-) > > brian > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > It seems to me you could replace it all with one query, something like this: SELECT discipline, COUNT(1) FROM showcase WHERE EXISTS (SELECT * FROM showcase_item WHERE showcase_id = showcase.id LIMIT 1) GROUP BY discipline ORDER BY (discipline != 'other'), discipline; - Josh
В списке pgsql-general по дате отправления: