0

I'm dealing with the following data that comes from 2 different tables, example data:

Production BOM No. Item No. Item Description Total Qty.
013085 003856 Orange Fabric 60
013085 003867 Tan Fabric 5.3
013085 006604 Natural Fabric 4
013152 012670 Black Fabric 9.7
013258 003871 Green Fabric 7.9
013258 003915 L.Green Fabric 17.5

The desired result set would return the highest total qty. per production BOM No. as follows:

Production BOM No. Item No. Item Description Total Qty.
013085 003856 Orange Fabric 60
013152 012670 Black Fabric 9.7
013258 003915 L.Green Fabric 17.5

Right now the code looks like this:

SELECT
    PBH.[No_],
    PBL.[No_],
    PBL.[Description],
    SUM(PBL.[Quantity]) AS [Total Qty.]
FROM [ProdBOMHeader] PBH
LEFT JOIN [ProdBOMLine] PBL ON PBL.[BOM No_] = PBH.[No_]
LEFT JOIN [Item] I ON I.[No_] = PBL.[No_]
WHERE I.[Fabric] = '1'
GROUP BY PBH.[No_],PBL.[No_],PBL.[Description]

I tried utilizing ROW_NUMBER and TOP in some way but that resulted in either too many records still or too few. I think the answer may be to use some kind of SQL Window Function but I'm not sure how to put it together properly.

Also to keep in mind, I will need to use this as a CTE as part of a larger query, so I can't use ORDER BY in any way.

Dale K
  • 25,246
  • 15
  • 42
  • 71
adhocEY
  • 75
  • 6
  • 3
    `ROW_NUMBER() OVER (PARTITON BY PBH.[No_] ORDER BY SUM(PBL.[Quantity]) DESC)` then filter that to 1. Side note: your left-join is actually an inner join, due to the `where` – Charlieface May 11 '23 at 23:58
  • @Charlieface Okay the code works great and the row numbers are being accurately labeled. But I am struggling a bit to filter them all to only show Row 1. How do I make reference to the Row No. column in the WHERE clause properly? – adhocEY May 12 '23 at 00:24
  • It's all in that link. You need to filter on the outside (ie out it in a subquery or CTE), you can't filter on the same level as where you declare it – Charlieface May 12 '23 at 00:28
  • @Charlieface Was able to get it working with your clue to change to CTE, thanks so much for your help in resolving this! – adhocEY May 12 '23 at 00:43

0 Answers0