SQLTeam.com | Weblogs | Forums

Select MAX in one column for each instance in another column (probably worded incorrectly)

#1

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!

#2

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 :smile:

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
#3 
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.