I am creating a cte with a partition that orders datetime2 stamps in descending count and applying row_number(), and then selecting row 1 to get the most recent date.
with cte1 as
(
select Customer_Number, event, Source_update_date, row_number() over
(partition by Customer_Number order by Source_update_date desc) row_num
from schema.table
where event = ''ACTIVE''
)
select Customer_Number, event
from cte1
where row_num = 1
I notice that the most recent date is selected, but not the largest .nnnnnnn part. how to account for this? its a DB2 server
the two dates in question are:
2018-03-27 09:22:52.0000230
2018-03-27 09:21:16.0000210 (this one is being selected as the top even though 210 is less than 230)
after reviewing my where statement I noticed that 2018-03-27 09:22:52.0000230 was being excluded, an alteration to the where statement is now producing correct results in the above query. I removed where event = ACTIVE since I realized sometimes the most recent event is CLOSE which was the case in the above customer_number.