How to get the last occurrence of a field

Question

Hi,

I have a simple query that returns to 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

Answer 1

Window function ROW_NUMBER() is handy for the task.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (WONum INT, OpNum DECIMAL(10,2));
INSERT INTO @tbl (WONum, OpNum) VALUES
(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 );
-- DDL and sample data population, end

;WITH rs AS
(
 SELECT WONum, OpNum
 , ROW_NUMBER() OVER (PARTITION BY WONum ORDER BY OpNum DESC) AS seq
 FROM  @tbl
)
SELECT WONum, OpNum
FROM rs
WHERE seq = 1
ORDER BY WONum;

Output

+---------+--------+
|  WONum  | OpNum  |
+---------+--------+
| 1396026 | 120.00 |
| 1427600 | 130.00 |
| 1456392 | 130.00 |
| 1458018 | 280.00 |
| 1460224 |  80.00 |
+---------+--------+

Answer 2

Hi @Alvord, Timothy ,

You can also try the following. Thanks!

 SELECT WONum, MAX(OpNum) as OpNum  
 FROM tblHistory  
 GROUP BY WONum  

----------

If the above response is helpful, please accept as answer and up-vote it. Thanks!

Answer 3

Or try:

    CREATE TABLE #test (WONum INT, OpNum DECIMAL(10,2));
    INSERT INTO #test values(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 );


    SELECT WONum,OpNum FROM (SELECT *,RANK() OVER(PARTITION BY WONum ORDER BY OpNum DESC) rr
                   FROM #test) t
    WHERE rr=1

Answer 4

I need to use this idea with a this query

SELECT        tblWTHistory.WONum, tblWTHistory.Date, tblWTHistory.OperatorBadgeID, tblWTHistory.PartNum, tblWTHistory.CellReceivedQty, tblWTHistory.EstCompDate, tblWTStatus.StatusName, tblWTReasons.ReasonName, 
                         tblWorkType.WorkTypeName, tblWTHistory.Rework
FROM            tblWTHistory INNER JOIN
                         tblWorkCenter ON tblWTHistory.WorkCenterID = tblWorkCenter.WorkCenterID INNER JOIN
                         tblWTStatus ON tblWTHistory.StatusID = tblWTStatus.StatusID INNER JOIN
                         tblWTReasons ON tblWTHistory.ReasonID = tblWTReasons.ReasonID INNER JOIN
                         tblWorkType ON tblWTHistory.WorkTypeID = tblWorkType.WorkTypeID
WHERE        (tblWorkCenter.WorkCenterName LIKE '%BRZ%')
ORDER BY tblWTHistory.Date, tblWTHistory.WONum

Need to return records with largest StatusID for each unique combo of WONum, OperatorBadgeID, PartNum