Re: Fantasy Football complex select - Mailing list pgsql-novice

> -----Original Message-----
> From: Rory Campbell-Lange [mailto:rory@campbell-lange.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@campbell-lange.net>
> <www.campbell-lange.net>
>