SQL Blues......Part 2 HELP!!!

From: mAd hAcker <madhackerboy_at_hotmail.com>
Date: 2000/07/05
Message-ID: <yWO85.382$87.2461@news1.mts.net>#1/1

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 Wed Jul 05 2000 - 00:00:00 CDT