Home » RDBMS Server » Server Administration » Group By: A much faster alternative
Group By: A much faster alternative [message #370952] Mon, 13 March 2000 05:23
Halvor
Messages: 3
Registered: March 2000
Junior Member
Previos Problem:
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.

Solution that works:
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 );

The very fast alternative without using Group by:
Radek_Sedmak@eurotel.cz wrote:
>
> SELECT MA.ITEM_KEY,MA.ITEM_NAME FROM ITEM MA
> WHERE REVISION=(SELECT MAX(REVISION) FROM ITEM SL WHERE SL.ITEM_KEY=MA.ITEM_KEY)

Uses less than 1 second.
The other alternatives with IN and Group By, uses approx. 18 seconds.

Measured with:
http://members.tripod.com/easydoc/dim.htm
Previous Topic: SQL Nav to run with Personal Oracle
Next Topic: Summing a column when including one value is based on the existence of another
Goto Forum:
  


Current Time: Tue Oct 20 09:03:57 CDT 2020