Re: HELP! Group By will only allow 2 fields.....Why?
From: mAd hAcker <madhackerboy_at_hotmail.com>
Date: 2000/07/06
Message-ID: <i3595.1906$87.5469_at_news1.mts.net>#1/1
Date: 2000/07/06
Message-ID: <i3595.1906$87.5469_at_news1.mts.net>#1/1
Klaus,
Thank you very much! I have being trying for days to get this going.....and I've gotten about 25 different suggestions.*g*
I appreciate your help! Keep up the good work!
Mh
-- "Klaus Zeuch" <KZeuch_at_hotmail.com> wrote in message news:8k2ktc$fo8$15$1_at_news.t-online.com...Received on Thu Jul 06 2000 - 00:00:00 CEST
> 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
> >
> >
> >
> >
>
>