OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Group By

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Group By [message #370938]

Fri, 10 March 2000 04:15

Halvor
Messages: 3
Registered: March 2000

Junior Member

I have an ITEM table which might have several revisions for each ITEM.
Like this
ITEM_KEY REVISION ITEM_NAME PRODUCT_TYPE
10000 V-0001 El. Card C
10000 V-0002 El. Card B
20000 V-0001 Cable D
20000 V-0002 Cable A
20000 V-0003 Cable B

I only want information for the latest revision.
I'm using:
SELECT ITEM_KEY, max(REVISION)
FROM ITEM group by ITEM_KEY

The problem:
I also want to show other information, like PRODUCT_TYPE.
How Do I get the corresponding PRODUCT_TYPE for the selected REVISION.
I can't use max(PRODUCT_TYPE). It will return A for ITEM 20000.
I now that the max REVISION is always the last record/line for an ITEM.
So doing this in MSACCESS I just use the Aggregate Operator LAST.
I haven't found an equivalent operator in Oracle.

--
Regards Halvor

A great Oracle tool:
http://members.tripod.com/easydoc/dim.htm
http://members.tripod.com/Nybbies/vb.htm

Report message to a moderator

Re: Group By [message #370940 is a reply to message #370938]

Fri, 10 March 2000 07:30

Paul
Messages: 164
Registered: April 1999

Senior Member

Halvor,
This should work:

SELECT A.ITEM_KEY,
A.ITEM_NAME,
A.PRODUCT_TYPE
FROM ITEM A
WHERE A.REVISION = (SELECT max(B.REVISION)
FROM ITEM B
WHERE B.ITEM_KEY = A.ITEM_KEY
group by B.ITEM_KEY );

Regards,
Paul

Report message to a moderator

Re: Group By [message #370942 is a reply to message #370938]

Fri, 10 March 2000 08:14

Atavur Rahaman S.A
Messages: 23
Registered: January 2000

Junior Member

Hello,

Good Day!

This is an another way to solve ur query......

SQL> SELECT item_key,revision,product_type FROM item1 WHERE (item_key,revision) IN
(SELECT item_key,MAX(revision) FROM item1 GROUP BY item_key)

Regards

Atavur Rahaman S.A

Report message to a moderator

Previous Topic:	Re: DML
Next Topic:	Audit column names in trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 09:56:06 CDT 2025