Thread: 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
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>
On Wed, 2002-05-29 at 04:18, Rory Campbell-Lange wrote: > 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. Hey, it's not football season!! That's not for 4 more months... -- +---------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://ronandheather.dhs.org:81 | | | | "I have created a government of whirled peas..." | | Maharishi Mahesh Yogi, 12-May-2002, | ! CNN, Larry King Live | +---------------------------------------------------------+
On 29/05/02, Ron Johnson (ron.l.johnson@cox.net) wrote:
> On Wed, 2002-05-29 at 04:18, Rory Campbell-Lange wrote:
> > 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.
>
> Hey, it's not football season!! That's not for 4 more months...
That isn't what most of the rest of the world thinks! But you can
convince me by telling me if I can do something like this:
select all the rows from a table except
(where there are 4 instances of this column).
(how is that for a 2 line summary of my last long question?!).
Thanks for any help.
Rory
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
> -----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>
>
try
SELECT sys_key FROM power GROUP BY sys_key HAVING count(*) > 4;
-----Original Message-----
From: Rory Campbell-Lange [mailto:rory@campbell-lange.net]
Sent: Wednesday, May 29, 2002 11:49 PM
To: Ron Johnson
Cc: Postgresql Novice List
Subject: Re: [NOVICE] Fantasy Football complex select
On 29/05/02, Ron Johnson (ron.l.johnson@cox.net) wrote:
> On Wed, 2002-05-29 at 04:18, Rory Campbell-Lange wrote:
> > 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.
>
> Hey, it's not football season!! That's not for 4 more months...
That isn't what most of the rest of the world thinks! But you can
convince me by telling me if I can do something like this:
select all the rows from a table except
(where there are 4 instances of this column).
(how is that for a 2 line summary of my last long question?!).
Thanks for any help.
Rory
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html