Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: HELP! Group By will only allow 2 fields.....Why?
Hi,
that query is simply not allowed according to standards dbms-vendors are trying to follow (for details see ansi sql and standard ISO/IEC 9075:1992)
One solution might be using a subquery with correlation to the outer query. Let's create an example:
create table high_scores (pers_id number(10), weight number(10), event_id number(10));
insert into high_scores values (1,100,1); insert into high_scores values (1,100,2); insert into high_scores values (1,50,1); insert into high_scores values (2,20,5); insert into high_scores values (2,90,45);
SQL> select pers_id, event_id, weight from high_scores t1 2 where t1.weight = (select max(weight)
3 from high_scores t2 4 where t1.pers_id = t2.pers_id); PERS_ID EVENT_ID WEIGHT ---------- ---------- ---------- 1 1 100 1 2 100 2 45 90
Why do we get pers_id twice? Because there are two records with identical top value. If we want to suppress that *and* are using oracle 8.1.6 we could try using analytical functions (quite an overkill for results of fishing):
SQL> select pers_id, event_id, weight,
2 row_number() over (partition by pers_id order by weight desc)
rank_in_partition
3 from high_Scores;
PERS_ID EVENT_ID WEIGHT RANK_IN_PARTITION
---------- ---------- ---------- ----------------- 1 1 100 1 1 2 100 2 1 1 50 3 2 45 90 1 2 5 20 2
To get the top results per person we only have to wrap another sql-statement around:
SQL> select * from (
2 select pers_id, event_id, weight,
3 row_number() over (partition by pers_id order by weight desc)
rank_in_partition
4 from high_Scores)
5 where rank_in_partition = 1;
PERS_ID EVENT_ID WEIGHT RANK_IN_PARTITION
---------- ---------- ---------- ----------------- 1 1 100 1 2 45 90 1
hth
Klaus
"mAd hAcker" <madhackerboy_at_hotmail.com> schrieb im Newsbeitrag
news:b8395.1887$87.5167_at_news1.mts.net...
> 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