Need help with select query statement

[rarubio1]

I need to write a query that will return the Work Order,first step, and step description from the following table where its Copletion date IS NULL.

WorkOrder Step Description CompletionDate
WO001 1 Mat. Injection 5/1/04
WOO01 2 Deburring 5/2/04
WO001 3 Inspection NULL
WO001 4 Shipping NULL
WO002 1 Grinding 5/2/04
WO002 2 Inspection NULL
WO002 3 Shipping NULL
WO003 1 Mat. Melting NULL
WO003 2 Inspection NULL
WO003 3 Shipping NULL

The query should produce the following result:
WO001,3,Inspection
WO002,2,Inspection
WO003,1,Mat. Melting

I tried the following syntax but it did not work for me:
SELECT WorkOrder, MIN(Step) AS step, Description
FROM tblWorkOrderDtl
WHERE (CompletionDate IS NULL)
GROUP BY WorkOrderNo

Could someone please help me with the syntax.

Thanks in advance

RR

 

[TJRTech]

The problem with your syntax is that you have Description as a non-aggregate in the SELECT but not listed in the GROUP BY clause. But, of course, you don't want to list it in the group by, because you want the description associated with the record of the min step.

I would be interested in the "BEST" way to do this as well.

I can see a sub-select, or a select from a select, or possibly a having clause come into play.

Others?

TR

 

[amorous]

Does this work:

SELECT DISTINCT TOP 1 WorkOrder,Step,Description WHERE
CompletionDate IS NULL Order By Step ASC

-VJ

 

[TJRTech]

No, that only gives the TOP 1...not all first completed steps of all work orders.

TR

 

[amorous]

oops i missed the FROM clause : )

-VJ

 

[swampBoogie]

SELECT WorkOrder, Step, Description
  FROM tblWorkOrderDtl t
 WHERE step in (
     select min(step)
       from tblWorkOrderDtl
      where CompletionDate is null
        and WorkOrder = t.WorkOrder)

 

[TJRTech]

That should work and is the best I could think of too (subselect).

TR

 

[rarubio1]

Thank you all. TJRTech, Your'e a genious!!! That worked

RR