How to get the last occurrence of a field

• tim8w

  Old Hand

  Points: 348

  More actions

  February 22, 2021 at 11:46 pm

  #3848759

  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

• The Dixie Flatline

  SSC Guru

  Points: 53281

  More actions

  February 23, 2021 at 5:13 pm

  #3849140

  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