Finding the max of two dates that have the same Id
Posted by mark123
Last Updated: August 06, 2016
  1159

Good day everyone this code will be beneficial to anyone who has faced the situation of having to write a query to find the lets say a customer's latest product they have purchased. Now you might say a simple Select (max) etc... query could do this, however what if there is a case the product_ids are different then the max will generate multiple rows to reflect the different product id's. The code below solve this problem:

Select Rn,product_info,order_date from
(
Select row_number() over(partition by user_id Order by max(order_date) desc) AS RN,
order_date, product_info from dummy_table
)t1
where t1.Rn = 1

/* Row number returns a number for each row so if a user repeats they are counted 
as 1,2,3 and so on. Since we are order by highest to lowest and partitioning by the user_id
the selected where rownumber = 1 gives you the id of the user with the latest 
product. */

/* for more info about the row_number() function see https://msdn.microsoft.com/en-us/library/ms186734.aspx*/