SNIPPET
2 Upvotes

Getting the Last Record Using Rank Function - T-SQL Example

SQL
Querying

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
	

By GregHe1979 - Last Updated Dec. 1, 2020, 3:02 p.m.

COMMENTS
RELATED SNIPPETS
Search Snippets by Tag: