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 Sun Jan 27 2002 - 23:47:09 CST
