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_at_news1.mts.net>#1/1
ERROR at line 1:
ORA-00979: not a GROUP BY expression
AND ce.pin = players.pin
AND st.state_code = ce.state_caught_in AND st.state_code IN ( SELECT state_code
ORDER BY MAX(ce.actual_points) DESC
Date: 2000/07/06
Message-ID: <b8395.1887$87.5167_at_news1.mts.net>#1/1
[Quoted] [Quoted] 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)
[Quoted] FROM catch_entries GROUP BY pin ORDER BY max(actual_points) DESC" Works just fine......but when I do this:I get an error message :
"SELECT pin, max(actual_points), adjusted_points
FROM catch_entries GROUP BY pin ORDER BY max(actual_points) DESC"
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, [Quoted] [Quoted] 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.playersWHERE 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 [Quoted] is allowed per player.
[Quoted] Thanks a lot....I do appreciate all help that is offered.....
-- MhReceived on Thu Jul 06 2000 - 00:00:00 CEST