Re: Query HELP : Need ID After Doing "Group By"

This, of course, does not answer the question.

Try this to return all of the desired columns:

select a2.id, a1.item_class, a1.version
from
(select item_class, max(version) as version from a group by item_class) a1, (select id, version from a) a2
where a2.version = a1.version
order by a2.id;

You should see:

        ID ITEM_CLASS VERSION

---------- -------------------- ----------
         3 ABC                        1885
         5 PQR                         825
         9 XYZ                          59

which is, I believe, the result you are after.

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<xB558.5614$aF1.20894_at_rwcrnsc53>...
> You can't get the id because you are grouping the rows together, there is no
> id since the id is different for each row.
> Jim
> "Mladen Gogala" <mgogala_at_earthlink.net> wrote in message
> news:pan.2002.01.28.00.42.09.160936.1284_at_earthlink.net...
> > This would, I suppose, be answer:
> >
> >
> > SQL> select distinct deptno,sum(sal) from emp group by deptno;
> >
> > DEPTNO SUM(SAL)
> > ---------- ----------
> > 10 8750
> > 20 10875
> > 30 9400
> >
> >
> >
> >
> > On Sun, 27 Jan 2002 22:36:46 -0500, contrapositive wrote:
> >
> > > This is an Oracle 8i database. Table A contains data as follows
> > > (simplified for the sake of clarity):
> > >
> > > ID ITEM_CLASS VERSION
> > > 1 ABC 12
> > > 2 ABC 61
> > > 3 ABC 1885
> > > 4 PQR 17
> > > 5 PQR 825
> > > 6 XYZ 1
> > > 7 XYZ 55
> > > 8 XYZ 56
> > > 9 XYZ 59
> > >
> > > You get the idea. If I do:
> > > select ITEM_CLASS, max(VERSION)
> > > from A
> > > group by ITEM_CLASS
> > >
> > > I get (as expected):
> > > ABC 1885
> > > PQR 825
> > > XYZ 59
> > >
> > > Now how can I also grab the corresponding ID? It seems really
> > > straightforward, and I know I can do it with a subquery, but I'm trying
> > > not to (it slows things down to a grind; these are large tables). Is
> > > there some trick maybe with Row ID? (Just thinking out loud here.) If a
> > > subquery is the only way, maybe someone can offer some ideas as to how I
> > > might optimize it for performance.
> > >
> > > By the way, I don't think ITEM_CLASS is indexed in this case. In fact I
> > > think ID the only field part of an index (since it is part of the
> primary
> > > key -- or at least unique). Thanks in advance.
> > >
> > > -jk
> > >
> > >
> > >
> > >
> > >
> > > [NOTE: I posted this once already from my ISP's news server. It may have
> > > appeared on some news servers, but I never saw it (or any responses) on
> > > mine, so I'm reposting it.]
> >
> > --
> > Mladen Gogala
Received on Mon Jan 28 2002 - 09:14:12 CST