Re: ORDER BY with exception

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