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

Include adjusted_points in you group by, i.e.

GROUP BY pin, adjusted_points

mAd hAcker wrote:

> 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