February 22, 2021 at 11:46 pm
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
February 23, 2021 at 5:13 pm
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply