Joining two tables and need to have MAX aggregate function in ON clause

Question

This is my code! I want to give a part id and purchase order id to my report and it brings all the related information with those specification. The important thing is that, if we have same purchase order id and part id we need the code to return the result with the highest transaction id. The following code is not providing what I expected. Could you please help me?

SELECT MAX(INVENTORY_TRANS.TRANSACTION_ID), INVENTORY_TRANS.PART_ID
, INVENTORY_TRANS.PURC_ORDER_ID, TRACE_INV_TRANS.QTY, TRACE_INV_TRANS.CREATE_DATE, TRACE_INV_TRANS.TRACE_ID
FROM INVENTORY_TRANS    
JOIN TRACE_INV_TRANS ON INVENTORY_TRANS.TRANSACTION_ID = TRACE_INV_TRANS.TRANSACTION_ID    
WHERE INVENTORY_TRANS.PART_ID = @PartID 
    AND INVENTORY_TRANS.PURC_ORDER_ID = @PurchaseOrderID    
GROUP BY TRACE_INV_TRANS.QTY, TRACE_INV_TRANS.CREATE_DATE, TRACE_INV_TRANS.TRACE_ID, INVENTORY_TRANS.PART_ID
, INVENTORY_TRANS.PURC_ORDER_ID

The sample of trace_inventory_trans table is :

part_id   trace_id    transaction id     qty       create_date     
x           1             10
x           2             11
x           3             12

the sample of inventory_trans table is :

transaction_id     part_id     purc_order_id
11                 x            p20
12                 x            p20

I wanted to have the result of biggest transaction which is transaction 12 but it shows me transaction 11

Answer 1

I would use a sub-query to find the MAX value, then join that result to the other table.

The ORDER BY + TOP (1) returns the MAX value for transaction_id.

SELECT 
   inv.transaction_id
  ,inv.part_id
  ,inv.purc_order_id
  ,tr.qty
  ,tr.create_date
  ,tr.trace_id
FROM 
  (
    SELECT TOP (1)
      transaction_id,
      part_id,
      purc_order_id
    FROM 
      INVENTORY_TRANS
    WHERE 
      part_id = @PartID
      AND 
      purc_order_id = @PurchaseOrderID
    ORDER BY 
      transaction_id DESC
  ) AS inv
JOIN 
  TRACE_INV_TRANS AS tr
    ON inv.transaction_id = tr.transaction_id;

Results:

+----------------+---------+---------------+------+-------------+----------+
| transaction_id | part_id | purc_order_id | qty  | create_date | trace_id |
+----------------+---------+---------------+------+-------------+----------+
|             12 | x       | p20           | NULL | NULL        |        3 |
+----------------+---------+---------------+------+-------------+----------+

Rextester Demo