Select records based on colum value

[RJL1]

Hello,

Ran into this and there may be an easy way to solve but I cannot seem to find it. Any help is appreciated. I have a list of order and the qty that the requested. However some of the order are for the finigh goods item and the components that make that finish item. So for example I have a display with 3 components. What I need is aa way to select only the finish good item if it has qty on it or the components if the finish good does not have a qty on it

Here is an example of the data nad what the expected results are

Order     Item          Component Qty          Finigh item Qty
000125    Display 1     0                      1
000125    Component 1   10                     0
000125    Component 2   15                     0
000125    Component 3   5                      0

000985    Component 1   10                     0
000985    Component 2   15                     0

For order 000125 I would need only the Display 1 record sicne they are ordering 1 display

000125    Display 1     0                      1

For order 000985 I would need both records since there is no display record for it

000985    Component 1   10                     0
000985    Component 2   15                     0

Any help with this is appreciated
Thanks
RJL

 

[lameid]

Looks like....

Where (A.Order IN (Select A.Order From A Group By A.Order Having Sum(A.[Finigh item Qty] > 0) And A.[Finigh item Qty] > 0)
 OR (A.Order IN (Select A.Order From A Group By A.Order Having Sum(A.[Finigh item Qty] = 0))

 

[imex]

Hi,

RJL1, you can also try something like this:

with CTE_Max as
(
    select [Order], max([Finigh item Qty]) as [Finigh item Qty]
    from MyTable
)

select t.* 
from CTE_Max as c
inner join MyTable as t
    on t.[Order] = c.[Order] and
       t.[Finigh item Qty] = c.[Finigh item Qty]

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]