Thanks SB.....one problem though. When there are duplicate values for the
max(points), it returns nothing....is there any way around this?
--
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:962827368.24009.0.pluto.d4ee154e_at_news.demon.nl...
> 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
