Analytic function trong SQL Server

Trình bày diễn biến một món hàng

--select * from test

WITH cteMain
AS
(
SELECT
id, merchant, email, date, pk,
--ROW_NUMBER() OVER (ORDER BY email) AS sn
ROW_NUMBER() OVER (partition BY id order by pk) AS sn
FROM
test
--WHERE
--id=1
)
--select * from cteMain
SELECT 
m.id, m.merchant, m.email, m.date,
sLead.email AS leadvalue,
sLeg.email AS legvalue, m.pk, m.sn, sLead.sn, sLeg.sn
FROM
cteMain AS m
LEFT OUTER JOIN cteMain AS sLead
ON sLead.sn = m.sn+1 and sLead.id=m.id
LEFT OUTER JOIN cteMain AS sLeg
ON sLeg.sn = m.sn-1 and sLeg.id=m.id
ORDER BY
m.pk

http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/