Fantasy Football complex select - Mailing list pgsql-novice

I have the task of designing the office fantasy football system to run
on our intranet. The fantasy football game is a game centering around
the world cup football tournament.

The game is to make up a football team of 11 players where no more than
one country is represented, and no more or less than 1 goalkeeper, 4
full-backs, 4 midfielders and 2 forwards.

I'm having trouble making a selection from a join between the players
and team tables, based on the selections table. The selection is for a
particular "team"

Task A.
I need to return all the rows in players NOT matching the following
criteria based on the selections in the selections table:
1.    countries of existing selections
2.    goalkeeper if a goalkeeper selected
3.    full-back if 4 full-backs selected
4.    midfielders if 4 full-backs selected
5.    forwards if 2 forwards selected

I have managed 1. by doing:

    SELECT * FROM
    players
    WHERE country <> ALL (
        SELECT p.country FROM
        players p, selections s
        WHERE s.id_team = 2 AND s.id_player = p.code
    )
    ORDER BY country;

For 2-5 I can do the following selection, but don't know how to only
return values for postion if count of GOALKEEPERS is 1, count of
FORWARDS is 2, etc.

    SELECT DISTINCT p.position, count(*)
    FROM selections s, players p
    WHERE s.id_team = 2 AND s.id_player = p.code
    GROUP BY p.position;

      position   | count
    -------------+-------
     FORWARDS    |     2
     GOALKEEPERS |     1
     MIDFIELDERS |     3

Task B.
I'd like to sort the output on position in players.position in the
following order "GOALKEEPERS, FULL-BACKS, MIDFIELDERS, FORWARDS". How
can I specify a custom sort order? (I'm not sure what the order by
'expression' means in the \h help).

Huge thanks for any help!

Cheers
Rory

table "players" (excerpt from 736 rows)
  position   | code |       name       | country | caps | goals | points
-------------+------+------------------+---------+------+-------+--------
 FULL-BACKS  | 2204 | ODaf             | SEN     |   29 |     0 |      0
 FORWARDS    | 6060 | Edilson          | BRA     |   11 |     4 |      0
 GOALKEEPERS | 1001 | GBurgos          | ARG     |   35 |     0 |      0
 FULL-BACKS  | 2303 | SCherundolo      | USA     |    0 |     0 |      0
 MIDFIELDERS | 4296 | DMBeasley        | USA     |    7 |     1 |      0
 FORWARDS    | 6001 | GBatistuta       | ARG     |   74 |    55 |      0

table "teams"
 id |   name   |     members      |          ts_created
----+----------+------------------+-------------------------------
  1 | fantasy1 | geoff rory jenny | 2002-05-28 22:30:50.223285+01
  2 | tastics  | patrick uri nina | 2002-05-28 22:31:58.82615+01

table "selections"
 id_team | id_player | hidden |         ts_timestamp
---------+-----------+--------+-------------------------------
       2 |      1082 |      0 | 2002-05-28 22:35:22.956204+01
       2 |      6098 |      0 | 2002-05-28 22:35:58.677597+01
       2 |      6167 |      0 | 2002-05-28 22:36:05.445792+01
       2 |      4023 |      0 | 2002-05-28 22:37:12.909721+01
       2 |      4282 |      0 | 2002-05-28 22:37:20.489276+01
       2 |      4016 |      0 | 2002-05-28 22:37:28.286024+01

--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>