Select a column .....

• bull2000

  SSC Eights!

  Points: 841

  More actions

  March 11, 2005 at 6:41 am

  #76257

  I need to select the location which has the highest qty for each part for the following.

  How can I do it without using a temp table?

  I started with

  Select Part, Max(QTY) From Part_tbl  group by Part

  Thanks a lot for your help.

  here is the simplified version of my data:

  Part                    Qty                    Location

  P1                       100                    Loc-1

  P1                       200                    Loc-2

  P2                        222                   Loc-3

  P2                        111                   Loc-4

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  March 11, 2005 at 7:23 am

  #546607

  Select dtQties.Part, dtQties.Qty, P.Location FROM Part_tbl P inner

  join

  (Select Part, Max(QTY) as QTY From Part_tbl group by Part) dtQties on P.Part = dtQties.Part and P.Qty = dtQties.QTY

  ORDER BY dtQties.Part, P.Location --can have a tie for the max quantity