query help

[jcisco2]

I'm wondering if it is possible to pull out a distinct resulting set when the data is structured like this:

--raw data
part Number transType qtyReq qtyRec
1 ISS-SO 2 -2
2 ISS-SO 2 -2
3 ORD-SO -1 0
3 ISS-SO 2 -1
4 ORD-SO -3 0

--(no ISS-SO for part number 4)

--required result
part Number transType qtyReq qtyRec
1 ISS-SO 2 -2
2 ISS-SO 2 -2
3 ISS-SO 2 -1
4 ORD-SO -3 0

So basicly the query will need to be able to look at all the records and make a decision based on the if the result set has more than one of the same row and if it does then use the ISS-SO section and not the ORD-SO. and if the result set has no ISS-SO result use the ORD-SO values.

suggestions?

cheers.

 

[gmmastros]

If the only 2 valid values for transtype are iss-so and ord-so, then this query may work for you.

Select A.*
From   [!]TableName[/!] A
       Inner Join (
         Select PartNumber,
                Min(TransType) As TransType
         From   [!]TableName[/!]
         Group By PartNumber
         ) As B 
         On A.PartNumber = B.PartNumber
         And A.TransType = B.TransType

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom