Re: need help with a sql statement

"Ben" <benalvey_at_yahoo.com> schreef in bericht news: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 Received on Sat Jul 19 2008 - 04:16:33 CDT