Re: Fantasy Football complex select

> -----Original Message-----
> From: Rory Campbell-Lange [mailto:rory(at)campbell-lange(dot)net]
> Sent: 29 May 2002 10:18
> To: Postgresql Novice List
> Cc: Mike Anderson
> Subject: [NOVICE] Fantasy Football complex select
>
>
> 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
How about (untested):
SELECT * FROM
(SELECT DISTINCT p.position, count(*) AS pcnt
FROM selections s, players p
WHERE s.id_team = 2 AND s.id_player = p.code
GROUP BY p.position)
as sbsel
WHERE (position='GOALKEEPERS' AND pcnt=1) OR (posistion='FORWARDS' AND
pcnt=2);

>
> 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).
>

How about writeing a plpgsql function and then ordering by that
eg (untest):
CREATE FUNCTION ordr_plyr (text) RETURNS int4 AS '
DECLARE
plyr ALIAS FOR $1;
BEGIN
IF plyr='GOALKEEPERS'
RETURN 1;
ELSIF plyr='FULL-BACKS'
RETURN 2;
ELSIF plyr='MIDFIELDERS'
RETURN 3;
ELSIF plyr='FORWARDS'
RETURN 4;
ELSE
RETURN 5;
END IF;
END;
' LANGUAGE 'plpgsql' WITH (iscachable);
SELECT * FROM tbl ORDER BY ordr_plyr(tbl.position);

hth,
- Stuart
> 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(at)campbell-lange(dot)net>
> <www.campbell-lange.net>
>