Finding the Lowest ID Based On the Status Of Another Column?

Question

There are a lot of tips on the use of the MIN() function, but the situation below is more complicated because of multiple conditions. Each [Project] can have multiple [Work Order]s and each [Work Order] can have multiple [Op Seq ID]s.

TABLE: WorkOrders

Project	Work Order	Op Seq ID	Op Status Code	Op Name
A	100	10	Complete	Saw
A	100	20	Complete	Weld
A	100	30	NULL	Paint
A	100	40	NULL	Label
A	101	10	Complete	Laser
A	101	20	NULL	Drill
A	101	30	NULL	Paint
B	115	10	NULL	Laser
B	115	20	NULL	Drill

Within each [Project], I am looking to return each [Work Order] with the LOWEST [Op Seq ID] WHERE [Op Status Code] = NULL.

Project	Work Order	Op Seq ID	Op Status Code	Op Name
A	100	30	NULL	Paint
A	101	20	NULL	Drill
B	115	10	NULL	Laser

I started down the pathway with the following query, but got stuck trying to find the lowest Op Seq ID with an Op Status Code of NULL for each Work Order.

SELECT [WO Project ID], [Op Seq ID]
FROM dbo.WorkOrders T1
WHERE [WO Project ID] =
    (SELECT MIN([Op Seq ID]) 
    FROM dbo.WorkOrders T2
    WHERE T1.[WO Project ID] = T2.[WO Project ID])

score 0 · Answer 1 · answered Aug 04 '22 at 13:15

A community member pointed me to another similar post; with some editing the following query returns the correct results:

WITH cte AS
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY [Work Order ID] ORDER BY [Op Seq ID] ASC) AS rn
    FROM dbo.WorkOrders
    WHERE [Op Status Code] IS NULL
)
SELECT *
FROM cte
WHERE rn = 1

Finding the Lowest ID Based On the Status Of Another Column?

1 Answers1