How to get the last occurrence of a field

  • Hi,

    I have a simple query that returns two fields. I need to modify that query so that it only returns the last or highest number for each pair of fields.

     

    It's easier to show than explain. Here's my query:

    SELECT WONum, OpNum
    FROM tblHistory
    ORDER BY WONum, OpNum

     

    It returns something like this:

    WONum    OpNum

    1396026    40.00

    1396026    90.00

    1396026    90.00

    1396026  120.00

    1427600  130.00

    1456392  120.00

    1456392  130.00

    1458018  230.00

    1458018  280.00

    1460224  40.00

    1460224  80.00

    I need it to return:

    WONum    OpNum

    1396026  120.00

    1427600  130.00

    1456392  130.00

    1458018  280.00

    1460224  80.00

    Just the last row of each WONum, OpNum

    • This topic was modified 1 week, 2 days ago by  tim8w. Reason: typo
    • This topic was modified 1 week, 2 days ago by  tim8w. Reason: typo
  • Since you are sorting by OpNum within WONum, your "last occurrence" is the same thing as the maximum value of OpNum.  You even stated it was the "highest value."   So....

    select WONum, OpNum = max(OpNum)
    from [yourTable]
    group by woNum
    order by woNum

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply