Little Query Help

• 465789psw

  Hall of Fame

  Points: 3314

  More actions

  April 20, 2005 at 3:32 pm

  #164332

  I'm looking to get the highest ITEM_VEND_ID(IE 822297,ALL THREE ROWS)

  SELECT ITEM_NO,iv.ITEM_VEND_ID,PRICE,ivp.CTLG_NO

  FROM ITEM i,

       ITEM_VEND iv,

       ITEM_VEND_PKG ivp

     

  WHERE

      i.ITEM_ID = iv.ITEM_ID AND

      iv.ITEM_VEND_ID = ivp.ITEM_VEND_ID and

       i.ITEM_NO ='3501'

  ITEM_NO,ITEM_VEND_ID,PRICE,              CTLG_NO             

  3501         822295 16.7944    230000699H         

  3501         822295 5.5981     230000699H         

  3501         822295 .4665       230000699H         

  3501         822296 159.6636  230000699H         

  3501         822296 53.2209   230000699H         

  3501         822296 4.4350     230000699H         

  3501         822297 159.6636  230000699H         

  3501         822297 53.2210    230000699H         

  3501         822297 4.4350     230000699H      

   

  THANKS   

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  April 20, 2005 at 3:45 pm

  #553933

  SELECT ITEM_NO, iv.ITEM_VEND_ID, PRICE, ivp.CTLG_NO

  FROM ITEM i

  INNER JOIN ITEM_VEND iv ON i.ITEM_ID = iv.ITEM_ID

  INNER JOIN ITEM_VEND_PKG ivp ON iv.ITEM_VEND_ID = ivp.ITEM_VEND_ID

  WHERE i.ITEM_NO = '3501'

  AND iv.ITEM_VEND_ID = (SELECT MAX(ITEM_VEND_ID) FROM ITEM_VEND iv INNER JOIN ITEM i ON iv.ITEM_ID = i.ITEM_ID WHERE i.ITEM_NO = '3501')

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/

• 465789psw

  Hall of Fame

  Points: 3314

  More actions

  April 20, 2005 at 3:47 pm

  #553934

  NICE,NICE,NICE

   

  THANKS SO MUCH