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

From: Klaus Zeuch <KZeuch_at_hotmail.com>
Date: 2000/07/06
Message-ID: <8k2ktc$fo8$15$1_at_news.t-online.com>#1/1


Hi,

[Quoted] 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. [Quoted] 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 [Quoted] 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

[Quoted] "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 CEST

Original text of this message