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

Home -> Community -> Usenet -> c.d.o.misc -> Re: NEWBIE - Selecting MAX(Value) with connect by Prior

Re: NEWBIE - Selecting MAX(Value) with connect by Prior

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 08 Sep 2006 05:48:38 -0700
Message-ID: <1157719714.569594@bubbleator.drizzle.com>


VB wrote:
> I am running the following query to pull a Bill of Material (Product
> build structure) from our ERP suite (Oracle 8i)
>
> select PART_NO,PART_DESCRIPTION,
> LEVEL,ENG_CHG_LEVEL,ALTERNATIVE_NO,LINE_ITEM_NO,COMPONENT_PART,COMPONENT_DESCRIPTION,QTY_PER_ASSEMBLY
> from zz_bom_view t
> connect by prior component_part = part_no
> start with part_no = 'RB9044-00043'
>
> What I am looking for though is to flatten the results so it only
> returns the maximum revision (ENG_CHG_LEVEL) for the tree, ie the most
> up to date structure without the previous variations.
>
> I had tried MAX(ENG_CHG_LEVEL) and GROUP BY ENG_CHG_LEVEL but this
> threw up a "Not a Group by function" error.
>
> Could anyone please advise?

One way would be to wrap your existing SQL statement inside parentheses and then select a single from it. The overhead with an inline view is generally minimal.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Fri Sep 08 2006 - 07:48:38 CDT

Original text of this message

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