I am unable to figure out how to change my code due to change in requirements.
My previous requirement was to show the value of the NAME column corresponding to maximum DUE_DT for which I wrote the following SQL.
WITH T AS
(
SELECT 'R1' as row_id, 'N1' as name, '1-jan-2012' as start_dt, '1-nov-2012' as due_dt, 'Done' as status FROM DUAL UNION ALL
SELECT 'R1', 'N2', '2-jan-2012', '2-nov-2012', 'Pending' FROM DUAL UNION ALL
SELECT 'R1', 'N3', '3-jan-2012', '3-nov-2012', 'Done' FROM DUAL UNION ALL
SELECT 'R1', 'N4', '4-jan-2012', '4-nov-2012', 'Unassigned' FROM DUAL UNION ALL
SELECT 'R2', 'N1', '3-feb-2012', null, null FROM DUAL UNION ALL
SELECT 'R2', 'N2', '2-feb-2012', null, null FROM DUAL UNION ALL
SELECT 'R2', 'N5', '1-feb-2012', null, null FROM DUAL )
SELECT row_id, First_value(due_dt) OVER (PARTITION BY row_id ORDER BY due_dt desc NULLS LAST) output
FROM t;
However, now the requirement has changed to show the value of NAME column corresponding to maximum DUE_DT and STATUS = 'Done' and DUE_DT AND START_DT are not null.
So the output of the SQL should change to;
R1 3-nov-2012
R1 3-nov-2012
R1 3-nov-2012
R1 3-nov-2012
R2 null
R2 null
R2 null
P.S. - I cannot add any filters to WHERE clause, use GROUP BY, use subquery, inline query since this is just a part of the entire SQL. I am expecting some CASE WHEN statements/ some other analytic function to achieve the solution.