need help with a sql statement

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 Received on Fri Jul 18 2008 - 08:09:46 CDT