Re: ORDER BY with exception - Mailing list pgsql-general

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.


> 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:

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)

This should give you the total number of showcases that have been
accepted for each discipline. (DDL and data below.)

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.

Michael Glaesemann
grzm seespotcode net

CREATE TABLE discipline
(
     id INTEGER NOT NULL UNIQUE
     , name TEXT PRIMARY KEY
);

INSERT INTO discipline (id, name) VALUES
(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');

SELECT *
FROM discipline
ORDER BY name;

SELECT *
FROM discipline
ORDER BY name = 'other', name;


CREATE TABLE showcase
(
     id INTEGER NOT NULL UNIQUE
     , name TEXT PRIMARY KEY
     , discipline_id INTEGER NOT NULL
         REFERENCES discipline(id)
     , accepted BOOLEAN NOT NULL
);

INSERT INTO showcase (id, name, discipline_id, accepted)
VALUES
     (1, 'foo', 1, true)
     , (2, 'bar', 2, true)
     , (3, 'baz', 1, true)
     , (4, 'quux', 1, false)
     , (5, 'blurfl', 2, false);

CREATE TABLE showcase_item
(
     id INTEGER NOT NULL UNIQUE
     , description TEXT NOT NULL
     , showcase_id INTEGER NOT NULL
         REFERENCES showcase (id)
     , PRIMARY KEY (description, showcase_id)
);

INSERT INTO showcase_item (id, description, showcase_id)
VALUES
(1, 'a', 1)
, (2, 'b', 1)
, (3, 'c', 1)
, (4, 'd', 2)
, (5, 'e', 2)
, (6, 'f', 2)
, (7, 'g', 3)
, (8, 'h', 3)
, (9, 'i', 4)
, (10, 'j', 5);

SELECT *
FROM showcase;
id |  name  | discipline_id | accepted
----+--------+---------------+----------
   1 | foo    |             1 | t
   2 | bar    |             2 | t
   3 | baz    |             1 | t
   4 | quux   |             1 | f
   5 | blurfl |             2 | f
(5 rows)

SELECT *
FROM showcase
JOIN showcase_item ON (showcase.id = showcase_id);
id |  name  | discipline_id | accepted | id | description | showcase_id
----+--------+---------------+----------+----+-------------
+-------------
   1 | foo    |             1 | t        |  1 | a
|           1
   1 | foo    |             1 | t        |  2 | b
|           1
   1 | foo    |             1 | t        |  3 | c
|           1
   2 | bar    |             2 | t        |  4 | d
|           2
   2 | bar    |             2 | t        |  5 | e
|           2
   2 | bar    |             2 | t        |  6 | f
|           2
   3 | baz    |             1 | t        |  7 | g
|           3
   3 | baz    |             1 | t        |  8 | h
|           3
   4 | quux   |             1 | f        |  9 | i
|           4
   5 | blurfl |             2 | f        | 10 | j
|           5
(10 rows)