Re: SQL Blues......Part 2 HELP!!!

You haven't stated the version of Oracle. The type of query you are trying to write is why Oracle introduced the 'analytic functions' in 8.1.6 -

You need to introduce a column

    rank() over (player order by points) as ranked then add a where clause

    and ranked = 1

(I may have the syntax slightly fuzzy here) (You also have the option to choose a
dense_rank() rather than rank()).

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

mAd hAcker wrote in message ...


>Okay,  I've tried about everything....I've taken everyone's advice (albeit

>conflicting) one at a time, and still to no avail.

>    I am trying to retrieve the top values (determined by actual_points)

>from a table (catch_entries) for each individual player.

>ie:  bill's top score, mike's top score, jim's top score, etc.

>    Here is the most recent form of my select statement, which returns only

>one value - the highest actual_point value overall.

>

>    What do I need to do to make it return the highest actual_points value

>per user?????

>

>SELECT  ce.pin as pin, ce.date_caught as date_caught,

>      sp.name as specie_name, st.name as state_name,

>      INITCAP(players.first_name ||' '|| players.last_name) as player_name,

>      ce.specie_length as specie_length, ce.adjusted_points as

>adjusted_points,

>      ce.actual_points as actual_points

>FROM  dogfish.catch_entries ce, dogfish.species sp,

>      dogfish.states st, dogfish.players

>WHERE  ce.specie_id = sp.specie_id

>AND ce.pin = players.pin

>AND  st.state_code = ce.state_caught_in

>AND  st.state_code IN ( SELECT  state_code

>                                     FROM  dogfish.states

>                                     WHERE  region_id = in_region )

>AND actual_points = ( SELECT MAX(actual_points)

>                                     FROM  catch_entries ce1

>                                     WHERE  ce1.pin = pin)

>ORDER BY ce.actual_points DESC

> ;

>

>Thanks for any help.....I really appreciate it.

>--

>Mh

>

>