I have a table with 3 columns: Part, Location, and Quantity. All 3 columns may contain duplicates; each part may be in several different locations, and each location may have several parts. Quantities are all over the place.
I need a query that will select each part only once, along with the location for that part, having the highest quantity.
I have tried to use the Select MAX function in all different ways, but I always get either the MAX quantity with the wrong location, or not the MAX quantity, or multiple locations for each quantity...
I hope this is clear, I'm a bit of a newbie. Any help would be appreciated, Thanks!
I assume that for a given PART & LOCATION combination there is a max of only one row?
Dunno if this is the best way, but its one way
SELECT Part, Location, Quantity
FROM
(
SELECT [T_RowNumber] = ROW_NUMBER()
OVER
(
PARTITION BY Part, Location
ORDER BY Part, Location, Quantity DESC
),
Part, Location, Quantity
FROM dbo.MyTable
) AS T
WHERE T_RowNumber = 1
ORDER BY Part, Location
SELECT Part, Location, Quantity
FROM dbo.MyTable t1
WHERE Quantity =
(
SELECT MAX(Quantity) from dbo.myTable t2
WHERE T1.Part = T2.Part
)
This will, of course, print out two or more instances of Part/Location/Quantity, if two or more locations have the same Quantity of that part.
1 Like