add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- With nulls
CREATE TABLE T (Id INT IDENTITY(1,1), Value INT)
INSERT T
VALUES
(NULL), (11), (22), (33),
(NULL), (44), (55), (66),
(NULL), (77), (88), (99),
(NULL)

-- No nulls
CREATE TABLE T2 (Id INT IDENTITY(1,1), Value INT)
INSERT T2
VALUES
(11), (22), (33),
(44), (55), (66),
(77), (88), (99)

-- Naming key for following queries:
-- I = Ignore nulls
-- SP = Strictly preceding - Unbounded preceding through prior row
-- P = Unbounded preceding through current row
-- R = Range +/- 3 rows preceding/following
-- F = Current row through unbounded following
-- SF = Strictly following - next row through unbounded following
-- U = Unbounded Unbounded preceding through unbounded following

22 rows affected
SELECT *
, LAG(Value) OVER(ORDER BY Id) AS Lag
, LAG(Value) IGNORE NULLS OVER(ORDER BY Id) AS LagI
, LEAD(Value) OVER(ORDER BY Id) AS Lead
, LEAD(Value) IGNORE NULLS OVER(ORDER BY Id) AS [LeadI ???]
, FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS [FirstISF (= expected LeadI)]
FROM T
ORDER BY Id
Id Value Lag LagI Lead LeadI ??? FirstISF (= expected LeadI)
1 null null null 11 null 11
2 11 null null 22 null 22
3 22 11 11 33 11 33
4 33 22 22 null 22 44
5 null 33 33 44 33 44
6 44 null 33 55 33 55
7 55 44 44 66 44 66
8 66 55 55 null 55 77
9 null 66 66 77 66 77
10 77 null 66 88 66 88
11 88 77 77 99 77 99
12 99 88 88 null 88 null
13 null 99 99 null 99 null
SELECT *
, LAG(Value) OVER(ORDER BY Id) AS Lag
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LastSP
, LEAD(Value) OVER(ORDER BY Id) AS Lead
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS FirstSF
FROM T
ORDER BY Id

SELECT *
, LAG(Value) IGNORE NULLS OVER(ORDER BY Id) AS LagI
, LAST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LastISP
, LEAD(Value) IGNORE NULLS OVER(ORDER BY Id) AS [LeadI ???]
, FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS FirstISF
FROM T2
ORDER BY Id

SELECT *
, LAG(Value) OVER(ORDER BY Id) AS Lag
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LastSP
, LEAD(Value) OVER(ORDER BY Id) AS Lead
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS FirstSF
FROM T2
ORDER BY Id

Id Value Lag LastSP Lead FirstSF
1 null null null 11 11
2 11 null null 22 22
3 22 11 11 33 33
4 33 22 22 null null
5 null 33 33 44 44
6 44 null null 55 55
7 55 44 44 66 66
8 66 55 55 null null
9 null 66 66 77 77
10 77 null null 88 88
11 88 77 77 99 99
12 99 88 88 null null
13 null 99 99 null null
Id Value LagI LastISP LeadI ??? FirstISF
1 11 null null null 22
2 22 11 11 11 33
3 33 22 22 22 44
4 44 33 33 33 55
5 55 44 44 44 66
6 66 55 55 55 77
7 77 66 66 66 88
8 88 77 77 77 99
9 99 88 88 88 null
Id Value Lag LastSP Lead FirstSF
1 11 null null 22 22
2 22 11 11 33 33
3 33 22 22 44 44
4 44 33 33 55 55
5 55 44 44 66 66
6 66 55 55 77 77
7 77 66 66 88 88
8 88 77 77 99 99
9 99 88 88 null null
SELECT *
, FIRST_VALUE(Value) OVER(ORDER BY Id) AS First
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS FirstSP
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FirstP1
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS UNBOUNDED PRECEDING) AS FirstP2
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS FirstR
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS FirstF
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS FirstSF
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FirstA
FROM T
ORDER BY Id

SELECT *
, FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id) AS FirstI
, FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS FirstISP
, FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FirstIP1
, FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS UNBOUNDED PRECEDING) AS FirstIP2
, FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS FirstIR
, FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS FirstIF
, FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS FirstISF
, FIRST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FirsIA
FROM T
ORDER BY Id
SELECT *
, FIRST_VALUE(Value) OVER(ORDER BY Id) AS First
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS FirstSP
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FirstP1
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS UNBOUNDED PRECEDING) AS FirstP2
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS FirstR
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS FirstF
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS FirstSF
, FIRST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FirstA
FROM T2
ORDER BY Id
Id Value First FirstSP FirstP1 FirstP2 FirstR FirstF FirstSF FirstA
1 null null null null null null null 11 null
2 11 null null null null null 11 22 null
3 22 null null null null null 22 33 null
4 33 null null null null null 33 null null
5 null null null null null 11 null 44 null
6 44 null null null null 22 44 55 null
7 55 null null null null 33 55 66 null
8 66 null null null null null 66 null null
9 null null null null null 44 null 77 null
10 77 null null null null 55 77 88 null
11 88 null null null null 66 88 99 null
12 99 null null null null null 99 null null
13 null null null null null 77 null null null
Id Value FirstI FirstISP FirstIP1 FirstIP2 FirstIR FirstIF FirstISF FirsIA
1 null null null null null 11 11 11 11
2 11 11 null 11 11 11 11 22 11
3 22 11 11 11 11 11 22 33 11
4 33 11 11 11 11 11 33 44 11
5 null 11 11 11 11 11 44 44 11
6 44 11 11 11 11 22 44 55 11
7 55 11 11 11 11 33 55 66 11
8 66 11 11 11 11 44 66 77 11
9 null 11 11 11 11 44 77 77 11
10 77 11 11 11 11 55 77 88 11
11 88 11 11 11 11 66 88 99 11
12 99 11 11 11 11 77 99 null 11
13 null 11 11 11 11 77 null null 11
Id Value First FirstSP FirstP1 FirstP2 FirstR FirstF FirstSF FirstA
1 11 11 null 11 11 11 11 22 11
2 22 11 11 11 11 11 22 33 11
3 33 11 11 11 11 11 33 44 11
4 44 11 11 11 11 11 44 55 11
5 55 11 11 11 11 22 55 66 11
6 66 11 11 11 11 33 66 77 11
7 77 11 11 11 11 44 77 88 11
8 88 11 11 11 11 55 88 99 11
9 99 11 11 11 11 66 99 null 11
SELECT *
, LAST_VALUE(Value) OVER(ORDER BY Id) AS Last
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LastSP
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LastP1
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS UNBOUNDED PRECEDING) AS LastP2
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS LastR
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LastF
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS LastSF
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastA
FROM T
ORDER BY Id

SELECT *
, LAST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id) AS LastI
, LAST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LastISP
, LAST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LastIP1
, LAST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS UNBOUNDED PRECEDING) AS LastIP2
, LAST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS LastIR
, LAST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LastIF
, LAST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS LastISF
, LAST_VALUE(Value) IGNORE NULLS OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastIA
FROM T
ORDER BY Id

SELECT *
, LAST_VALUE(Value) OVER(ORDER BY Id) AS Last
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LastSP
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LastP1
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS UNBOUNDED PRECEDING) AS LastP2
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS LastR
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LastF
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS LastSF
, LAST_VALUE(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastA
FROM T2
ORDER BY Id
Id Value Last LastSP LastP1 LastP2 LastR LastF LastSF LastA
1 null null null null null 33 null null null
2 11 11 null 11 11 null null null null
3 22 22 11 22 22 44 null null null
4 33 33 22 33 33 55 null null null
5 null null 33 null null 66 null null null
6 44 44 null 44 44 null null null null
7 55 55 44 55 55 77 null null null
8 66 66 55 66 66 88 null null null
9 null null 66 null null 99 null null null
10 77 77 null 77 77 null null null null
11 88 88 77 88 88 null null null null
12 99 99 88 99 99 null null null null
13 null null 99 null null null null null null
Id Value LastI LastISP LastIP1 LastIP2 LastIR LastIF LastISF LastIA
1 null null null null null 33 99 99 99
2 11 11 null 11 11 33 99 99 99
3 22 22 11 22 22 44 99 99 99
4 33 33 22 33 33 55 99 99 99
5 null 33 33 33 33 66 99 99 99
6 44 44 33 44 44 66 99 99 99
7 55 55 44 55 55 77 99 99 99
8 66 66 55 66 66 88 99 99 99
9 null 66 66 66 66 99 99 99 99
10 77 77 66 77 77 99 99 99 99
11 88 88 77 88 88 99 99 99 99
12 99 99 88 99 99 99 99 null 99
13 null 99 99 99 99 99 null null 99
Id Value Last LastSP LastP1 LastP2 LastR LastF LastSF LastA
1 11 11 null 11 11 44 99 99 99
2 22 22 11 22 22 55 99 99 99
3 33 33 22 33 33 66 99 99 99
4 44 44 33 44 44 77 99 99 99
5 55 55 44 55 55 88 99 99 99
6 66 66 55 66 66 99 99 99 99
7 77 77 66 77 77 99 99 99 99
8 88 88 77 88 88 99 99 99 99
9 99 99 88 99 99 99 99 null 99
SELECT *
, MIN(Value) OVER(ORDER BY Id) AS Min
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MinSP
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MinP1
, MIN(Value) OVER(ORDER BY Id ROWS UNBOUNDED PRECEDING) AS MinP2
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS MinR
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinF
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS MinSF
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MinA
FROM T
ORDER BY Id

SELECT *
, MIN(Value) OVER(ORDER BY Id) AS Min
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MinSP
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MinP1
, MIN(Value) OVER(ORDER BY Id ROWS UNBOUNDED PRECEDING) AS MinP2
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS MinR
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinF
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS MinSF
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MinA
FROM T2
ORDER BY Id
Id Value Min MinSP MinP1 MinP2 MinR MinF MinSF MinA
1 null null null null null 11 11 11 11
2 11 11 null 11 11 11 11 22 11
3 22 11 11 11 11 11 22 33 11
4 33 11 11 11 11 11 33 44 11
5 null 11 11 11 11 11 44 44 11
6 44 11 11 11 11 22 44 55 11
7 55 11 11 11 11 33 55 66 11
8 66 11 11 11 11 44 66 77 11
9 null 11 11 11 11 44 77 77 11
10 77 11 11 11 11 55 77 88 11
11 88 11 11 11 11 66 88 99 11
12 99 11 11 11 11 77 99 null 11
13 null 11 11 11 11 77 null null 11 
Warning: Null value is eliminated by an aggregate or other SET operation.
Id Value Min MinSP MinP1 MinP2 MinR MinF MinSF MinA
1 11 11 null 11 11 11 11 22 11
2 22 11 11 11 11 11 22 33 11
3 33 11 11 11 11 11 33 44 11
4 44 11 11 11 11 11 44 55 11
5 55 11 11 11 11 22 55 66 11
6 66 11 11 11 11 33 66 77 11
7 77 11 11 11 11 44 77 88 11
8 88 11 11 11 11 55 88 99 11
9 99 11 11 11 11 66 99 null 11
SELECT *
, MIN(Value) OVER(ORDER BY Id) AS Min
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MinSP
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MinP1
, MIN(Value) OVER(ORDER BY Id ROWS UNBOUNDED PRECEDING) AS MinP2
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS MinR
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinF
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS MinSF
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MinA
FROM T
ORDER BY Id

SELECT *
, MIN(Value) OVER(ORDER BY Id) AS Min
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS MinSP
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MinP1
, MIN(Value) OVER(ORDER BY Id ROWS UNBOUNDED PRECEDING) AS MinP2
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS MinR
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MinF
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS MinSF
, MIN(Value) OVER(ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MinA
FROM T2
ORDER BY Id
Id Value Min MinSP MinP1 MinP2 MinR MinF MinSF MinA
1 null null null null null 11 11 11 11
2 11 11 null 11 11 11 11 22 11
3 22 11 11 11 11 11 22 33 11
4 33 11 11 11 11 11 33 44 11
5 null 11 11 11 11 11 44 44 11
6 44 11 11 11 11 22 44 55 11
7 55 11 11 11 11 33 55 66 11
8 66 11 11 11 11 44 66 77 11
9 null 11 11 11 11 44 77 77 11
10 77 11 11 11 11 55 77 88 11
11 88 11 11 11 11 66 88 99 11
12 99 11 11 11 11 77 99 null 11
13 null 11 11 11 11 77 null null 11 
Warning: Null value is eliminated by an aggregate or other SET operation.
Id Value Min MinSP MinP1 MinP2 MinR MinF MinSF MinA
1 11 11 null 11 11 11 11 22 11
2 22 11 11 11 11 11 22 33 11
3 33 11 11 11 11 11 33 44 11
4 44 11 11 11 11 11 44 55 11
5 55 11 11 11 11 22 55 66 11
6 66 11 11 11 11 33 66 77 11
7 77 11 11 11 11 44 77 88 11
8 88 11 11 11 11 55 88 99 11
9 99 11 11 11 11 66 99 null 11