Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2010
    Posts
    38

    Unanswered: Sum one row from joined table

    Hi!

    I have this SQL so far:
    SELECT (SUM(TotalQty) - SUM(ProdQty)) AS 'openProd' FROM PUB.ProdOper INNER JOIN PUB.ProdOrder ON (ProdOper.PrOrderNum=ProdOrder.PrOrderNum) WHERE ProdOrder.ItemCode = '1234' AND ProdOrder.Completed = 0

    The problem is I want to sum only the last row from table ProdOper (where ProdOper.PosNum is the highest). How can I do that?

    Thanks!

  2. #2
    Join Date
    Feb 2010
    Posts
    38
    I thought I'd explain it a little better:

    There are tables ProdOrder and ProdOper.

    ProdOrder
    =======
    OrderNum
    1001
    1002

    ProdOper
    =======
    OrderNum, PosNum
    1001, 500
    1001, 510
    1002, 600
    1002, 610

    How do I select, from ProdOper, only the rows with the highest PosNum? It's a Progress database.
    Last edited by tosa; 11-05-10 at 04:04.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    then add in a subselect that you a.Posnum = (select max(Posnum) from table b where a.Ordernum = b.Ordernum)
    Dave

  4. #4
    Join Date
    Feb 2010
    Posts
    38
    Thanks for your reply.

    I didn't get it to work though.

    I don't get it, this query

    "SELECT * FROM PUB.ProdOper AS l INNER JOIN PUB.ProdOrder AS o ON l.PrOrderNum=o.PrOrderNum WHERE o.ItemCode = '2073' AND l.Completed = 0 AND o.Completed = 0 AND l.OperPosNum = (SELECT MAX(OperPosNum) FROM PUB.ProdOper)"

    returns no rows at all, but this

    "SELECT * FROM PUB.ProdOper AS l INNER JOIN PUB.ProdOrder AS o ON l.PrOrderNum=o.PrOrderNum WHERE o.ItemCode = '2073' AND l.Completed = 0 AND o.Completed = 0 AND l.OperPosNum = '515'"

    returns two rows.
    Last edited by tosa; 11-08-10 at 04:47.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT something
         , anything
         , just_not_the_dreaded_evil_select_star
      FROM PUB.ProdOrder AS o
    INNER 
      JOIN ( SELECT PrOrderNum
                  , MAX(OperPosNum) AS last_posnum
               FROM PUB.ProdOper
              WHERE Completed = 0
             GROUP
                 BY PrOrderNum ) AS m
        ON m.PrOrderNum = o.PrOrderNum 
    INNER
      JOIN PUB.ProdOper AS l 
        ON l.PrOrderNum = o.PrOrderNum 
       AND l.OperPosNum = m.last_posnum
     WHERE o.ItemCode = '2073' 
       AND o.Completed = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2010
    Posts
    38
    Thanks Rudy! This looks quite close.

    I'd like to select the rows from table ProdOper with highest OperPosNum, but I'm not sure how to modify your query to do it...

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what makes you think my query needs to be modified?

    did you test it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2010
    Posts
    38
    I'm terribly sorry!

    Eeh...I used the "evil" star again and didn't notice the stuff I actually wanted was added at the end.

    Thanks again! You're a pro!

Posting Permissions

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