Keep Max value from a unqiue fields 1

[acjordan]

Basicly this is the problem.
I have one value that can have multiples and have a quanity attached to it. I want to return that value once and get the highest quanity. (ie. NAME, ORDER QUANITY - Joe 5, Joe 15 Joe 12 it would return Joe 15)

Thanks
For your help!

 

[al1024]

select NAME
, max(ORDER_QTY)
from MyTable
group by NAME

 

[al1024]

Perhaps did you need something else in your record, such as an ORDER_ID for example.
Try this :

select	A.NAME
	,	A.ORDER_ID
	,	A.ORDER_QTY
from	MyTable	as A
where	exists (	select	1
					from	MyTable as B
					where	A.NAME = B.NAME
					having	A.ORDER_QTY = max(B.ORDER_QTY)
				)
;

 

[acjordan]

Thanks thats just what I need I don't know why I didn't think of it.