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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: 2000/07/08
Message-ID: <963076131.328.0.nnrp-14.9e984b29_at_news.demon.co.uk>#1/1


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
>
>
Received on Sat Jul 08 2000 - 00:00:00 CEST

Original text of this message