How to bring row values along for the ride with aggregates?

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?

I’m not exactly sure from your post what you need exactly, but I’m pretty sure you can use SQL Windowing with a Calc field something like this to get there:

SUM(PartBin.OnHandQty) Over(PARTITION PartBin.BinNum ORDER BY PartBin.BinNum)

Perhaps you can clarify what you are after?

I did a blog post on this type of thing, and it provides a bit more complex example of Aggregates Over… also known as Windowing:

1 Like

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. :thinking:

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

So it took me a while to figure out, but I got it working. That’s will be a nice solution for the future.

@Rick_Bird, I see now how this is a version of how windowing can be applied for my solution.

Thanks for the knowledge guys. This will be super helpful in the future.


I have to give you guys a gold start for helping me understand this windowing stuff now. I will be able to skip a lot of sub queries for summing up random stuff using this!

1 Like

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.

Thanks a lot @Banderson finally I knew how the function works.

Your welcome? Lol. Not sure what I did.

What did your calculated field end up looking like?