CR9 

I want to return just 1 of these 4 rows but my standard embedded (ROWID = (SELECT     MAX(invtr1.ROWID)…… returns nothing

Edited by carstowal - 02 Nov 2009 at 9:02am

I was misleading in my question.  my apologies

I don't really want just one row where (WORKORDER_BASE_ID = '141605')

I only had that workorder_base_ID in the command so I could evaluate whether or not the results were correct.

I really want one row for each existing workorder_base_ID where

(WORKORDER_SUB_ID = 0)
AND (TYPE = 'I')
AND (CUST_ORDER_ID IS NOT NULL)

I can't suppress details or my totals will be wrong.

I don't know how to do or what a stored procedure is. 

I can create a view of the table but I still need the SQL statement to do that.

I don't know how Distinct can help me.  I don't want every distinct combination.


There can be multiple Cust_Order_ID to any given WorkOrder_Base_ID
There can be multiple WorkOrder_Base_ID to any given Cust_Order_ID
in addition the Cust_Order_ID could be NULL.

I don't care which Cust_Order_ID the results return, as long as it only returns one Cust_Order_ID per WorkOrder_Base_ID and Cust_Order_ID isn't NULL

Example:

ROW   WO                  CO
row1...123.................AAA
row2...123.................BBB
row3...123.................AAA
row4...456.................AAA
row5...456.................CCC
row6...456.................<NULL>

I want my results to return
only (1) of Rows 1, 2, 3 -don't care which
and
only (1) of Rows 4, 5     -don't care which
but
NEVER Row 6