Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> REPOST: Query HELP : Need ID After Doing "Group By"
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.]
This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Sun Jan 27 2002 - 21:36:46 CST