I have a table showing production steps (PosID) for a production order (OrderID) and which machine (MachID) they will be run on; I’m trying to reduce the table to show one record for each order – the lowest position (field “PosID”) that is still open (field “Open” = Y); i.e. the next production step for the order.

Example data I have:

Example result I want:

I’ve tried two approaches, but I can’t seem to get either to work:

1. I don’t want to put “MachID” in the GROUP BY because that gives me all the records that are open, but I also don’t think there is an appropriate aggregate function for the “MachID” field to make this work.

    SELECT “OrderID”, MIN(“PosID”), “MachID”
    FROM Table T0
    WHERE “Open” = ‘Y’
    GROUP BY “OrderID”
   

2. With this approach, I keep getting error messages that T1.”PosID” (in the JOIN clause) is an invalid column. I’ve also tried T1.MIN(“PosID”) and MIN(T1.”PosID”).

    SELECT T0.“OrderID”, T0.“PosID”, T0.“MachID”
    FROM Table T0
    JOIN
        (SELECT “OrderID”, MIN(“PosID”)
        FROM Table
        WHERE “Open” = ‘Y’
        GROUP BY “OrderID”) T1
    ON T0.”OrderID” = T1.”OrderID”
    AND T0.”PosID” = T1.”PosID”
   

CodePudding user response：

Try this:

SELECT “OrderID”,“PosID”,“MachID” FROM (
SELECT 
T0.“OrderID”, 
T0.“PosID”, 
T0.“MachID”,
ROW_NUMBER() OVER (PARTITION BY “OrderID” ORDER BY “PosID”) RNK
FROM Table T0
WHERE “Open” = ‘Y’
) AS A 
WHERE RNK = 1

I've included the brackets when selecting columns as you've written it in the question above but in general it's not needed.

What it does is it first filters open OrderIDs and then numbers the OrderIDs from 1 to X which are ordered by PosID

After it filters on the "rnk" column indicating the lowest PosID per OrderID. ROW_NUMBER() in the select clause is called a window function and there are many more which are quite useful.

P.S. Above solution should work for MSSQL