I’m thinking I’m missing something pretty easy here. I’m trying to use the TOP function in a subquery, but it’s not returning the top one for each link back to the main query. To simplify my example, I’ve got a main query against Part where TypeCode = “P”, and I want to see the PartBin record with the HIGHEST OnHandQty. So, I’ve got a subquery on PartBin, sort by PartBin.OnHandQty (Descending), and on the Subquery Options I specify it as a TOP query with “1” in the Rows Number.
To link the query back to the main, I link on Part Num. When I run the query, I get ONE ROW period - just the part that happens to have the highest on-hand inventory. I want ALL parts with inventory, but to see which bin has the highest for each of them.
The SQL it’s showing looks like this:
select
[Part].[PartNum] as [Part_PartNum],
[SubQuery2].[PartBin_WarehouseCode] as [PartBin_WarehouseCode],
[SubQuery2].[PartBin_BinNum] as [PartBin_BinNum],
[SubQuery2].[PartBin_OnhandQty] as [PartBin_OnhandQty]
from Erp.Part as Part
inner join (select top (1)
[PartBin].[PartNum] as [PartBin_PartNum],
[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
[PartBin].[BinNum] as [PartBin_BinNum],
[PartBin].[OnhandQty] as [PartBin_OnhandQty]
from Erp.PartBin as PartBin
order by PartBin.OnhandQty Desc) as SubQuery2 on
Part.PartNum = SubQuery2.PartBin_PartNum
where (Part.TypeCode = 'P')
order by Part.PartNum
The problem is that the inner join is just returning one row. I really need a where clause on the inside of the inner join, so I get one row per part.
The subquery you have there returns just one row. You need a group by along with a max aggregate function there. See below and attached.
select
[Part].[Company] as [Part_Company],
[Part].[PartNum] as [Part_PartNum],
[sqOnhand].[PartBin_WarehouseCode] as [PartBin_WarehouseCode],
[sqOnhand].[PartBin_BinNum] as [PartBin_BinNum],
[sqOnhand].[Calculated_MaxOnHand] as [Calculated_MaxOnHand]
from Erp.Part as Part
inner join (select
[PartBin].[Company] as [PartBin_Company],
[PartBin].[PartNum] as [PartBin_PartNum],
[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
[PartBin].[BinNum] as [PartBin_BinNum],
(max(PartBin.OnhandQty)) as [Calculated_MaxOnHand]
from Erp.PartBin as PartBin
group by [PartBin].[Company],
[PartBin].[PartNum],
[PartBin].[WarehouseCode],
[PartBin].[BinNum]) as sqOnhand on
Part.Company = sqOnhand.PartBin_Company
and Part.PartNum = sqOnhand.PartBin_PartNum
where (Part.TypeCode = 'P')
order by Part.PartNum
What I needed was for the subquery to return all the data but with a rank by sort order within Part by quantity. That meant rank #1 for each part was the highest bin quantity for that part. The main query then just had to grab the subquery row with Rank #1.
Subquery calculated field RANK:
ROW_NUMBER() over (PARTITION BY PartBin.PartNum ORDER BY PartBin.OnhandQty DESC)
Then in the main query, just added a condition on the subquery where Calculated_Rank = 1