Re: SQL Blues.....

add
and catch_entries.actual_points =
(select max(actual_points
  from catch_entries ce1
  where ce1.pin = catch_entries.pin)

Hth,

Sybrand Bakker, Oracle DBA

"mAd hAcker" <madhackerboy_at_hotmail.com> wrote in message news:uTL85.311$87.1920_at_news1.mts.net...
> 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