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

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