I want to be able to make list bins with the max(onhandqty), and grab the corresponding bin number. Is there an easy way to grab the rest of the corresponding row information in a query? If I include the bin number, it grabs all of the bins.
The workaround I have done for this before is I have made a subquery with the aggregate, then rejoined the original table to the summarized one. Sometimes this works, other times it doesn’t, depending on what I am looking for.
Does anyone have an easier solution for this common problem?
That’s very interesting and very useful, (and I’ll have to keep that in my back pocket) but not exactly what I am looking for. Although, I don’t fully understand how the function works yet, so it just may be that the example that is in the blog post just isn’t the same as what I am looking for.
Here’s a better explanation.
Imagine you have part number 123. Part number 123 is located in 10 different bins, each with various quantities in them. I want to be able to do a query for the bin location that has the highest quantity in it. If I use the max function, I can find what the highest quantity is, but it won’t show me which bin number has the highest quantity. The reason that I need this in this case, it I want to be able to run a query to feed into DMT to do inventory transfers to move inventory from the highest quantity bin, into bins with most negative inventory. I need 1 bin for the max, and 1 bin for the max negative for the from and to bins. If I have double rows, this gets messed u, and the it will transfer twice.
If I use the rejoining method, I have a problem in that if the highest quantity is say, 10, but there are 2 bins with that quantity, I now have 2 rows. It’s also occurring to me as I am typing this, that no matter what function or technique I would use to get the bin number with the max, there has to be some sort of tie break function in order for any system to be able to get to one row.
For a single value/column, I can use the technique below, but many times you need more than one column returned, so that can be tedious, as you would have to set up a sub query for each column that I want returned. In this example, I need bin location and warehouse. Maybe it’s the best option we have though.
What do you think should be the #1? if you don’t really care try something like this. This creates a unique row_number count for your list. Then you can select the number of the list most likely 1 to be used as your top on.
[PartRevA].[PartNum] as [PartRevA_PartNum],
[PartRevA].[RevisionNum] as [PartRevA_RevisionNum],
[PartRevA].[RevShortDesc] as [PartRevA_RevShortDesc],
[PartRevA].[Company] as [PartRevA_Company]
from Erp.PartRev as PartRevA
inner join (select
[PartRevB].[PartNum] as [PartRevB_PartNum],
[PartRevB].[RevisionNum] as [PartRevB_RevisionNum],
(ROW_NUMBER() OVER (PARTITION BY partrevb.PartNum ORDER BY partrevb.ApprovedDate DESC)) as [Calculated_PartRevB_RowNum]
from Erp.PartRev as PartRevB
where (PartRevB.Approved = 1 and PartRevB.EffectiveDate <= getdate())) as PartRevB1 on
PartRevA.PartNum = PartRevB1.PartRevB_PartNum
PartRevA.RevisionNum = PartRevB1.PartRevB_RevisionNum
where PartRevB1.Calculated_PartRevB_RowNum = 1
Hi @Banderson do you have an example about using row number by chance? I’m trying to do something similar you did get the max Extended price from all the lines and get which part belongs to that record to then link it to the part table and get some data from there.