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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 28 Jan 2002 10:56:44 -0800
Message-ID: <a346tc02j3c@drn.newsguy.com>

In article <4e07f6b1.0201271936.584c4958_at_posting.google.com>, contrapositive_at_hotmail.com says...
>
>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.
>

either of these will do it, the first one is a "trick" but can be very efficient in some cases as it requires but one pass. Note: if item_class,version is NOT unique -- these two queries return DIFFERENT answers. The first query returns SOME id for the max version, the second query will return them all. Also, make sure to use a format that is "sortable" for version -- tricky if version can be negative)

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select item_class,

  2         to_number(substr(data,1,10)) version,
  3         to_number(substr(data,11))    id

  4 from (
  5 select item_class, max( to_char(version,'fm0000000009') || id ) data   6 from t
  7 group by item_class
  8 )
  9 /

ITE VERSION ID
--- ---------- ----------

ABC       1885          3
PQR        825          5
XYZ         59          9

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select item_class, version, id   2 from t
  3 where (item_class, version) in ( select item_class, max(version)

  4                                      from t
  5                                     group by item_class )

  6 /

ITE VERSION ID
--- ---------- ----------

ABC       1885          3
PQR        825          5
XYZ         59          9

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

>-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.]

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Jan 28 2002 - 12:56:44 CST