Funciones FIRST_VALUE y LAST_VALUE

¿Conoces las funciones FIRST_VALUE y LAST_VALUE de T-SQL? Si no las conoces, deberías ya que son unas funciones analíticas que facilitan muchas consultas. Y si las conoces, también deberías seguir leyendo, porque te explico alguna cosilla que deberías tener en cuenta si vas a usarlas.

Como su propio nombre indican, FIRST_VALUE devuelve el primer valor de un conjunto ordenado de valores y LAST_VALUE el último.

Ambas funciones siempre van acompañadas de la cláusula OVER, dónde se indica el orden que se va a aplicar.

Opcionalmente, puedes aplicar estas funciones sobre particiones, añadiendo la cláusula PARTITION BY dentro del OVER.

Como no quiero repetir la teoría, lo mejor es que revises toda la información más detallada en las páginas de referencia de Microsoft:

• FIRST_VALUE: https://learn.microsoft.com/es-es/sql/t-sql/functions/first-value-transact-sql?view=sql-server-ver16
• LAST_VALUE: https://learn.microsoft.com/es-es/sql/t-sql/functions/last-value-transact-sql?view=sql-server-ver16

Lo que realmente me interesa contarte, es cómo funcionan realmente ambas funciones y evitar que cometas el mismo error que yo cometí en su día. Y lo mejor para ello es usar un ejemplo.

Vamos usar una tabla temporal, con los siguientes valores

declare @tabla1 table
(
    id int
    ,importe int
    ,fecha datetime
)
 
INSERT INTO @tabla1 (Id,Importe,fecha)
values (1,100,'20190101')
,(1,200,'20190102')
,(1,300,'20190103')
,(2,400,'20190101')
,(2,500,'20190102')
,(2,600,'20190103')
 
SELECT * from  @tabla1

Ahora, con estos datos, queremos sacar de cada ID su primer y su último importe y fecha. El instinto te puede decir que es tan simple como usar FIRST_VALUE y LAST_VALUE, pero ¡ojo! ¡No! ¡Mira lo que hace!

Select DISTINCT
    id
    ,FIRST_VALUE(importe) OVER(PARTITION BY ID ORDER BY Fecha) as PrimerImporte
    ,FIRST_VALUE(fecha) OVER(PARTITION BY ID ORDER BY Fecha) as FechaPrimerImporte
    ,LAST_VALUE(importe) OVER(PARTITION BY ID ORDER BY Fecha) as UltimoImporte
    ,LAST_VALUE(fecha) OVER(PARTITION BY ID ORDER BY Fecha) as FechaUltimoImporte
from @tabla1

 Si lanzamos esta SELECT obtenemos la siguiente información:

Id	PrimerImporte	FechaPrimerImporte	UltimoImporte	FechaUltimoImporte
1	100	2019-01-01 00:00:00.000	100	2019-01-01 00:00:00.000
1	100	2019-01-01 00:00:00.000	200	2019-01-02 00:00:00.000
1	100	2019-01-01 00:00:00.000	300	2019-01-03 00:00:00.000
2	400	2019-01-01 00:00:00.000	400	2019-01-01 00:00:00.000
2	400	2019-01-01 00:00:00.000	500	2019-01-02 00:00:00.000
2	400	2019-01-01 00:00:00.000	600	2019-01-03 00:00:00.000

Para empezar, me devuelve N líneas por cada ID, cuando sólo debería devolver una. Y es que aunque usemos la cláusla DISTINCT,  el LAST_VALUE me devuelve un valor diferente en cada fila: te va diciendo el último lleva hasta el momento de procesar cada una de las líneas de la tabla particionada por ID.

Como ves, el LAST_VALUE, a mi parecer, es un confuso, así que yo no lo uso (al menos en las casuísticas habituales). Si realmente necesito conocer el ÚLTIMO valor, lo enfoco por otro lado: «el ÚLTIMO VALOR no es otro que el 1º empezando por el final», así que opto por usar el FIRST_VALUE y simplemente, ordeno al revés.

Select DISTINCT
	id
	,FIRST_VALUE(importe) OVER(PARTITION BY ID ORDER BY Fecha) as PrimerImporte
	,FIRST_VALUE(fecha) OVER(PARTITION BY ID ORDER BY Fecha) as FechaPrimerImporte
	,FIRST_VALUE(importe) OVER(PARTITION BY ID ORDER BY Fecha DESC) as UltimoImporte
	,FIRST_VALUE(fecha) OVER(PARTITION BY ID ORDER BY Fecha DESC) as FechaUltimoImporte
from @tabla1

Y así, el resultado, sí sería el esperado.

id	PrimerImporte	FechaPrimerImporte	UltimoImporte	FechaUltimoImporte
1	100	2019-01-01 00:00:00.000	300	2019-01-03 00:00:00.000
2	400	2019-01-01 00:00:00.000	600	2019-01-03 00:00:00.000

¡Espero haberte ayudado con esta pequeña explicación y con este ejemplo!