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.