Issue

This Content is from Stack Overflow. Question asked by Kavitha kavi

ID Status OfferID
1 Processed 456
1 Processed 123
2 Pending 999
3 Processed 678
3 Pending 789

Based on ID,

if ID=1 and having same status then output column(CTSDate) should be max of offerID
IF ID=2 and having only one status then output is offerID associated with it
If ID=3 and having different Status then pick Processed status related OfferID

The above scenario need to be worked for a wholeset of data.

Please help.

Solution

We can use ROW_NUMBER() here with appropriate sorting levels:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ID
                                   ORDER BY Status DESC, OfferID DESC) rn
    FROM yourTable t
)

SELECT ID, Status, OfferID
FROM cte
WHERE rn = 1
ORDER BY ID;

This Question was asked in StackOverflow by Kavitha kavi and Answered by Tim Biegeleisen It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.