JOIN on aggregate function

Question

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:

OrderID	PosID	MachID	Open
1	1	A	N
1	2	B	Y
1	3	C	Y
2	4	C	Y
2	5	D	Y
2	6	E	Y

Example result I want:

OrderID	PosID	MachID
1	2	B
2	4	C

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

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”
```

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”

Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — philipxy, Jun 15 '22 at 09:34
[mre] [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Jun 15 '22 at 09:56

score 0 · Accepted Answer · answered Jun 15 '22 at 09:46

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

OrderID	PosID	MachID	Open	RNK
1	2	B	Y	1
1	3	C	Y	2
2	4	C	Y	1
2	5	D	Y	2
2	6	E	Y	3

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

JOIN on aggregate function

1 Answers1