Re: need help with a sql statement

On Jul 19, 5:16 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Ben" <benal..._at_yahoo.com> schreef in berichtnews:a0aea362-c256-4c5a-a1bf-2a9ccbbb8844_at_p25g2000pri.googlegroups.com...
> On Jul 18, 9:09 am, Ben <benal..._at_yahoo.com> 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, c.name, 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, c.name
> > 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?
> ==============================================
>
> If I were you, I'd write a procedure. It's a lot easier to debug and to
> change if your rules might change.
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

good point Received on Mon Jul 21 2008 - 10:51:59 CDT