CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
timmack
timmack

Reputation: 590

Getting syntax error using the Lead/Lag function in SQL Server 2012

I'm new to using the Lead/Lag function in SQL Server 2012 and I got the syntax error when I tested it in SQL Server Management Studio. I used the lead and lag function for the purpose of fetching previous and next row values.

SQL Syntax I used:

SELECT 
    Id, Name, Address, Age, Nationality,
    LEAD(Name, 1) OVER (ORDER BY Id ) LeadValue,
    LAG(Name, 1) OVER (ORDER BY Id ) LagValue
FROM 
    Table

Error message:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Table'.

Please advise what I've missed here. Thanks

Upvotes: 2

Views: 1017

Answers (1)

Deadsheep39
Deadsheep39

Reputation: 611

Table is keyword. Please avoid to use reserved words and keywords.

If your table have name Table, add brackets like [Table].

List of reserved words and keywords: https://msdn.microsoft.com/en-us/library/ms189822(v=sql.110).aspx

Second task in comment:

if object_id('tempdb..#Tmp') is not null drop table #Tmp
select *, row_number() over (order by Id) RN
into #Tmp
from [Table] 

select t.* 
from #Tmp t, (select Id, max(RN) mx, min(RN) mi from #Tmp group by Id) f
where t.Id = f.Id
    and (f.mx = t.RN or f.mi = t.RN)

Upvotes: 2

Related Questions