SQL Blues.....

From: mAd hAcker <madhackerboy_at_hotmail.com>
Date: 2000/07/05
Message-ID: <uTL85.311$87.1920@news1.mts.net>#1/1

Okay...I have a select statement, which works fine, gives me everything I need...but I need to make a modification. I want to create a similar query that will return the same results, but with only the top entry (determined by actual_points) per player.
ie: a player will have 100 entries, the current statement returns all entries, ranked by points. The new statement should return only 1 (the top) entry per player ranked again by points. Here's the current statement......

 CURSOR c_all_entries
 IS
 SELECT catch_entries.date_caught, species.name as specie_name,   states.name as state_name,
  initcap(players.first_name ||' '|| players.last_name) as player_name,   catch_entries.specie_length, catch_entries.adjusted_points, catch_entries.actual_points
 FROM catch_entries, species, states, players  WHERE catch_entries.specie_id = species.specie_id  AND catch_entries.pin = players.pin
 AND states.state_code = catch_entries.state_caught_in  AND states.state_code IN ( SELECT state_code

                                             FROM  dogfish.states
                                             WHERE  region_id =

in_variable )
 ORDER BY catch_entries.actual_points DESC ;

I have tried a bunch of different things, but nothing seems to be returning what I need....All/any help is greatly appreciated. Thanks,
Mh
__ Received on Wed Jul 05 2000 - 00:00:00 CDT