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

On 27 Jan 2002 19:36:46 -0800, contrapositive_at_hotmail.com (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?

Try this:

SELECT

	Item_Class,
	Version,
	(SELECT Id FROM A WHERE Version = Outer.Version)
FROM
	(select ITEM_CLASS, max(VERSION) from A
               group by ITEM_CLASS) OUTER;

Brian Received on Mon Jan 28 2002 - 08:32:58 CST