Select Statement Help

Results 1 to 2 of 2

Thread: Select Statement Help

  1. #1
    Join Date
    Dec 1969

    Default Select Statement Help

    I wrote this statement:<BR><BR> SELECT PRODUCT_INFO.NBR, PRODUCT_INFO.DESC_LIN_1, <BR> NOTES.LIN1 AS Expr1, <BR> PRODUCT_INFO.REG_PRC AS Expr2<BR> FROM NOTES_IMAGES INNER JOIN<BR> PRODUCT_INFO ON <BR> NOTES_IMAGES.KEY_FLD = PRODUCT_INFO.NBR INNER JOIN<BR> NOTES ON PRODUCT_INFO.NBR = NOTES.KEY_FLD<BR> WHERE (PRODUCT_INFO.ECOMMERCE_FLG = N&#039;Y&#039;) AND <BR> (NOTES.SEQ_FLD = N&#039;EC Item Ext Desc 1&#039;) AND <BR> (NOTES_IMAGES.LIN1 = &#039;CBowls.jpg&#039;)<BR> ORDER BY PRODUCT_INFO.DisplayOrder<BR><BR>Which produces these results:<BR><BR> PFB1240 STEW PLATE (BLACK) "width 10""" 30<BR> PFB1250 "6"" BOWL (BLACK)" "width 6""" 20<BR> PFB1260 "4"" BOWL (BLACK)" "width 4""" 18<BR> PFC1240 STEW PLATE (CELADON) "width 10""" 30<BR> PFC1250 "6"" BOWL (CELADON)" "width 6""" 20<BR> PFC1260 "4"" BOWL (CELADON)" "width 4""" 18<BR> PFW1240 STEW PLATE (WHITE) "width 10""" 30<BR> PFW1250 "6"" BOWL (WHITE)" "width 6""" 20<BR> PFW1260 "4"" BOWL (WHITE)" "width 4""" 18<BR><BR>What I really want it to generate is this:<BR><BR> PFB1240 STEW PLATE (BLACK) "width 10""" 30<BR> PFB1250 "6"" BOWL (BLACK)" "width 6""" 20<BR> PFB1260 "4"" BOWL (BLACK)" "width 4""" 18<BR><BR>Which is based on the distinct widths. If someone could help me, that would be great. I&#039;ve been fooling around with distinct and Min for a very long time.<BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969
    Indianapolis, IN

    Default RE: Select Statement Help

    Okay, I don&#039;t know which one is the width field in your query, but for what you&#039;re wanting to do, you need to try GROUP BY. Anything that you want to group should be in that clause. Now, what that means is that anything not within your GROUP BY needs to be an aggregate (SUM, MAX, MIN, etc.) Since you have text fields, you&#039;ll probably want MIN or MAX on those other fields. Does that make sense?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts