CODEWITHSUNDEEP
sqlsql-server
kazzi
kazzi

Reputation: 534

Conditional filter with row numbers

I have a sample code below of containing an ID, a Date, a Value, along with a row numbered that is partitioned by the ID holder and ordered by their meeting date:

SELECT
     c.ID
    ,m.CONTACT_DATE
    ,d.TEST
    ,row_number() over(partition by c.ID 
                       order by m.CONTACT_DATE desc
                      )     [rn]
FROM COMMUNITY C
INNER JOIN MEETING m
    ON c.ID = m.CONTACT_ID
LEFT JOIN DISCUSSION d
    ON m.DISCUSSION_TEST = d.TEST

A sample of the results of running such a query would bring:

ID      CONTACT_DATE        TEST        rn
01      2017-05-01          NULL        1
01      2017-04-01          1           2
01      2017-03-01          NULL        3
02      2017-08-01          NULL        1
02      2017-09-01          NULL        2
02      2017-10-01          1           3
03      2017-02-01          NULL        1
03      2017-01-01          NULL        2

What I'd like to do is group each of the IDs to get the most recent CONTACT_DATE (ie. Place in subquery T, then WHERE T.rn = 1 GROUP BY T.ID)

However, if there's a value under TEST, then instead I want to see the most recent CONTACT_DATE that has a value, like below:

ID      CONTACT_DATE        TEST        rn
01      2017-04-01          1           2
02      2017-10-01          1           3
03      2017-02-01          NULL        1

What can I do to filter the most recent CONTACT_DATE that has a value under TEST, while still getting the most recent CONTACT_DATE if all values for that ID is NULL?

Upvotes: 1

Views: 564

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

You can change your row_number ordering:

row_number() over(partition by c.ID 
                       order by CASE WHEN d.TEST IS NOT NULL THEN 1 ELSE 2 END
                      , m.CONTACT_DATE desc
                      )

Upvotes: 4

Related Questions