first_value_vs_last_value_example

FIRST_VALUE vs. LAST_VALUE...WHICH ONE DO I USE?!?

/*Just use FIRST_VALUE. LAST_VALUE takes more coding (in SQL Server) to get the same thing done, such as the inclusion of a ROWS BETWEEN clause:

(Please note that this article applies to SQL Server. Redshift requires you to have a window function regardless of which method you use)

The below query tries to get the 'most recent' value without having to join back on itself. 
Two methods, last_value and first_value are used in the attempt:
*/

WITH DATES_EXAMPLE
AS
(SELECT
		*
	FROM (VALUES
	(999, CAST('01-JAN-2000' AS DATE), 'oldest'),
	(999, CAST('01-JAN-2007' AS DATE), 'less old'),
	(999, CAST('01-JAN-2017' AS DATE), 'most recent'),
	(989, CAST('01-JAN-2012' AS DATE), 'oldest'),
	(989, CAST('01-JAN-2013' AS DATE), 'less old'),
	(989, CAST('01-JAN-2015' AS DATE), 'most recent')
	)
	AS SOURCE (GRP_NUM, DT, VAL
	))


SELECT
	*
	,FIRST_VALUE(VAL) OVER (PARTITION BY GRP_NUM ORDER BY DT DESC) using_first_value
	,LAST_VALUE(VAL) OVER (PARTITION BY GRP_NUM ORDER BY DT ASC) using_last_value_incorrectly
	,LAST_VALUE(VAL) OVER (PARTITION BY GRP_NUM ORDER BY DT ASC ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING ) using_last_value_correct
FROM DATES_EXAMPLE
order by grp_num, dt