Re: need help with a sql statement

From: Ben <>
Date: Fri, 18 Jul 2008 07:37:40 -0700 (PDT)
Message-ID: <>

On Jul 18, 9:09 am, Ben <> wrote:
> I've got a personal project for myself and a group of friends. we've
> been playing poker together for about 5 years and decided to make a
> website to keep track of our league results using apex. The data model
> is pretty simple, it could be more complicated depending on what I
> wanted to add but this is the essential data needed:
> a players table
> (pkr_players) player_id num, name varchar2, email varchar2, phone
> varchar2
> a tournament header
> (pkr_tourn_hdr) tourn_id num, season num, trny_in_season num, tdate
> date, fee num, entrants num
> a tournament detail
> (pkr_tourn_det) tourn_id num, player_id num, paid char(1), rank num
> Our winner for an individual tournament is based on a calculation that
> takes into account the number or players in that tournament and your
> place of finish (rank). The winner for the season is based on the sum
> of those values but we drop the lowest two scores for each individual.
> So each player could but likely wont be participating in every
> tournament.
> Is there a single sql statement that could give me the winner for a
> season? I'm thinking I'll have to make a little procedure to calculate
> the scores for a given season.
> The following sql gives me the points total for everyone for a season,
> but doesn't take into account the dropping of the two lowest scores.
> calc_points is a function I made to return the points. Also, if
> someone misses a tournament they get a 0 for that tournament but
> nothing is entered into the pkr_tourn_det table.
> select a.season,, sum(calc_points(a.entrants, b.rank)) as
> TotalPoints
> from pkr_tourney_hdr a, pkr_tourn_det b, pkr_players c
> where a.tourn_id = b.tourn_id
> and b.player_id = c.player_id
> and a.season = 4
> group by a.season,
> order by sum(calc_points(a.entrants, b.rank)) desc

Also, would it make any difference if there was a record in tourn_det for every player and their rank was null if they didn't play? Received on Fri Jul 18 2008 - 09:37:40 CDT

Original text of this message