CODEWITHSUNDEEP
sqlsql-server
LearnerBee
LearnerBee

Reputation: 65

Extract only latest status lines

I have three status of an order as Entered,Aw Appr & Completed in my table. Entered and Completed have one row each while AW Appr repeat on weekly basis till the order got completed. Now all three status but only once. AW Appr should be latest one. I am using this query, but it's giving only maximum month and max week data. It's pick available max week of complete data instated latest entry. Please help to enhance query to fetch all three status and AW Appr only latest one.

Source Table http://sqlfiddle.com/#!18/67924/2

SELECT DISTINCT t1.Contract_Number
    ,t2.D_Monthx
    ,t2.D_Reporting_Week
    ,t2.D_Status
FROM Table1 t1
INNER JOIN (
    SELECT DISTINCT max(D_Monthx) D_Monthx
        ,Max(D_Reporting_Week) D_Reporting_Week
        ,D_Status
        ,Contract_Number
    FROM Table1
    GROUP BY Contract_Number
        ,D_Status
    ) t2 ON t1.Contract_Number = t2.Contract_Number
    AND t1.D_Monthx = t2.D_Monthx
WHERE t1.Contract_Number = '130100964/2'

Result Should be -

enter image description here

Upvotes: 1

Views: 60

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

We can try using ROW_NUMBER here for a straightforward solution:

SELECT Contract_Number, D_Monthx, D_Reporting_Week, D_Status
FROM
(
    SELECT D_Monthx, D_Reporting_Week, D_Status, Contract_Number,
        ROW_NUMBER() OVER (PARTITION BY Contract_Number, D_Status
                           ORDER BY D_Reporting_Week DESC) rn
    FROM yourTable
) t
WHERE rn = 1;

Demo

Row number works well here, because for the Entered and Completed statuses would only ever appears once, meaning their row numbers would always be one. Similarly, the row number for the most recent AW Appr which we want to select would also be one.

Upvotes: 4

Ajay Gupta
Ajay Gupta

Reputation: 1855

Using Row_Number()

Select Contract_Number, D_Monthx, D_Reporting_Week, D_Status from 
(Select *, 
Row_Number() over 
    (partition by Contract_Number,D_Status order by D_Monthx desc) 
    as ranking 
from Table1)c
where ranking=1

SqlFiddle

Upvotes: 4

Related Questions