HELP! Group By will only allow 2 fields.....Why?

From: mAd hAcker <madhackerboy_at_hotmail.com>
Date: 2000/07/06
Message-ID: <b8395.1887$87.5167@news1.mts.net>#1/1

My query will only allow 2 fields when I have a GROUP BY condition at the end....why?
For Example:

"SELECT pin, max(actual_points)

            FROM catch_entries
            GROUP BY pin
            ORDER BY max(actual_points) DESC"
Works just fine......but when I do this:


            "SELECT pin, max(actual_points), adjusted_points

            FROM catch_entries
            GROUP BY pin
            ORDER BY max(actual_points) DESC"

I get an error message :
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Why is this? it seems pretty simple..... Here's the whole query I am trying to get working :

 SELECT ce.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,
      max(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 )

 GROUP BY ce.pin
 ORDER BY MAX(ce.actual_points) DESC

The idea is to return the highest value(actual_points) per player as only 1 is allowed per player.

Thanks a lot....I do appreciate all help that is offered.....

--
Mh

Received on Thu Jul 06 2000 - 00:00:00 CDT