SQL Blues.....
Date: 2000/07/05
Message-ID: <uTL85.311$87.1920_at_news1.mts.net>#1/1
[Quoted] Okay...I have a select statement, which works fine, gives me everything I
[Quoted] need...but I need to make a modification. I want to create a similar query
[Quoted] that will return the same results, but with only the top entry (determined
by actual_points) per player.
[Quoted] 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)
[Quoted] [Quoted] 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
[Quoted] [Quoted] what I need....All/any help is greatly appreciated.
Thanks,
Mh
__
Received on Wed Jul 05 2000 - 00:00:00 CEST