Getting the Last Record Using Rank Function - T-SQL Example
SQL
This method uses the rank function in T-SQL to take get the latest order information for each PRODUCT_ID. We use assign each record a rank where rank partitions the records by PRODUCT_ID and orders them by latest ORDER_DATE first. In the outer query only records that have RANK=1 representing the last record for each PRODUCT_ID are kept.
/*RANK FUNCTION SYNTAX*/ RANK() OVER (PARTITION BY FIELD_A ORDER BY FIELD_B) /*RANK FUNCTION EXAMPLE*/ SELECT PRODUCT_ID, ORDER_DATE, ORDER_NO FROM (SELECT PRODUCT_ID, ORDER_DATE, ORDER_NO, RANK() OVER (PARTITION BY PRODUCT_ID ORDER BY ORDER_DATE DESC) AS RANK FROM ORDERS ) WHERE RANK = 1
5
4
4
3
3
3