Re: Query HELP!

hi !

may be solution....

select a.id,b.item_class,b.version
from table_a a,(select item_class,max(version) version from table_a group by item_class) b
where a.item_class=b.item_class and a.version=b.version

you can create the index on (item_class,version)

@+

"contrapositive" <nosp_at_m.com> a écrit dans le message news: 3c51f92a$1_2_at_nopics.sjc...
> 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:
> ABC 1885
> PQR 825
> XYZ 59
>
> Now how can I also grab the corresponding ID? Seems straightforward and
I'm
> trying to do it without subqueries as they slow things down to a grind
> (these are large tables). If a subquery is the only way, maybe someone can
> give me some ideas as to how I may optimize it for performance.
>
> By the way, I don't think ITEM_CLASS is indexed in this case. Thanks in
> advance.
>
> -jk
>
>
>
Received on Fri Jan 25 2002 - 19:20:21 CST