Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> REPOST: Query HELP : Need ID After Doing "Group By"

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

From: contrapositive <contrapositive_at_hotmail.com>
Date: 27 Jan 2002 19:36:46 -0800
Message-ID: <6$--$%%%_$$%$_-%-$@news.noc.cabal.int>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US