Previous and Next value in SQL Server 2012

Sql server 2012 provide next analytic function Lead() and lag(). 

Lag:  Accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2012. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

Lead : Accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2014. LEAD provides access to a row at a given physical offset that follows the current row. So here is example how to use these function in select query.

In this query    access data from a subsequent row (lead) and previous row (lag) in the same result set without the use of a self-join.

Suppose we have employee table it has following columns and records

ENO	ENAME	DEPTID	JOBTYPE	SALARY
101	Anil	10	Assistant	10000
102	Sunny	20	Analyst	50000
103	Karan	20	Clerk	12000
104	Sam	30	Assistant	20000
105	Peter	30	Manager	60000
106	Lucky	30	Sr analyst	55000
107	Sandy	40	Team Lead	44000

select ENO, ENAME,DEPTID,JOBTYPE,LAG(ENO) OVER (ORDER BY ENO) PreviousValue,

LEAD(ENO) OVER (ORDER BY ENO) Nextvalue from emp

When execute this query we have following output

ENO	ENAME	DEPTID	JOBTYPE	PREVIOUSVALUE	NEXTVALUE
101	Anil	10	Assistant	NULL	102
102	Sunny	20	Analyst	101	103
103	Karan	20	Clerk	102	104
104	Sam	30	Assistant	103	105
105	Peter	30	Manager	104	106
106	Lucky	30	Sr analyst	105	107
107	Sandy	40	Team Lead	106	NULL

  Previous value null first row and nextvalue in last because there were no previous rows  And  similar there were no next record