Msg 102, Level 15, State 1, Line 22 Incorrect syntax near 'order'.

Question

When i am executing below query in sql 2008 then it giving below error,

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'order'.

,But when i am executing in sql 2014 it is working fine,with same data ,what could be the issue.

WITH CTE
AS(SELECT isnull(CustomerItem.CIName,ItemMasterFile.Descriptionitem) as Descriptionitem,sum(tbl_PckDetail.QTY) as QY,
(isnull(Probale.PBSize,itemmasterfile.weight)) as WT,(sum(tbl_PckDetail.QTY)*isnull(Probale.PBSize,
itemmasterfile.weight))as Total_wt,('x '+ ''+' '+ ''+ ItemMasterFile.BaleSize) ITEM,
CAST((isnull(nullif(isnull(Probale.PBSize,itemmasterfile.weight),0)*sum(tbl_PckDetail.QTY)/nullif(2.2046,0),0)) as decimal(18,0)) Kg
FROM tbl_SBDispatachD
inner join tbl_PckDetail on tbl_PckDetail.PID=tbl_SBDispatachD.PID
inner join ItemMasterFile on ItemMasterFile.CodeItem=tbl_PckDetail.Codeitem
inner join SalesOrder ON SalesOrder.OrderNo = tbl_PckDetail.OrderNo
left join CustomerItem on CustomerItem.Codeitem=tbl_PckDetail.Codeitem
and CustomerItem.CustomerID=SalesOrder.Customerid
left outer join Probale on Probale.prdno=tbl_PckDetail.Prdno
and ItemMasterFile.CodeItem=Probale.codeitem
where tbl_SBDispatachD.ID =1109 and tbl_PckDetail.DelID is null and tbl_SBDispatachD.Del is null
group by CustomerItem.CIName,ItemMasterFile.Descriptionitem ,Probale.Pbsize,ItemMasterFile.weight ,ItemMasterFile.BaleSize)
,cte2 as(SELECT Descriptionitem,SUM (QY) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem) QY,SUM (WT) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem)WT,
SUM (Total_wt) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem) Total_wt,Item,SUM (kg) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem)kg
FROM CTE)
,cte3 as(select *,row_number() over(partition by Descriptionitem order by Descriptionitem)rn from cte2)
select * from cte3 where rn<2

Jignesh Kumar · Answer

Hi, You do not need to write cte2 and cte3 in above query, You need to remove these lines and add other lines Need to remove lines cte2 as ( SELECT Descriptionitem, SUM (QY) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem) QY, SUM (WT) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem)WT, SUM (Total_wt) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem) Total_wt, Item, SUM (kg) OVER(PARTITION BY Descriptionitem ORDER BY Descriptionitem)kg FROM CTE) , cte3 as ( select *,row_number() over(partition by Descriptionitem order by Descriptionitem)rn from cte2) Add This line, select Descriptionitem, SUM (QY) as QY, SUM (WT) as WT, SUM (Total) as Total, MAX (X), SUM (Kg) as Kg from CTE group by Descriptionitem Your final query looks like, WITH CTE AS ( SELECT isnull (CustomerItem.CIName,ItemMasterFile.Descriptionitem) as Descriptionitem, sum (tbl_PckDetail.QTY) as QY, ( isnull (Probale.PBSize,itemmasterfile.weight)) as WT,( sum (tbl_PckDetail.QTY)* isnull (Probale.PBSize, itemmasterfile.weight)) as Total_wt, 'x ' + '' + ' ' + '' + ItemMasterFile.BaleSize as X, CAST (( isnull ( nullif ( isnull (Probale.PBSize,itemmasterfile.weight),0)* sum (tbl_PckDetail.QTY)/ nullif (2.2046,0),0)) as decimal (18,0)) Kg FROM tbl_SBDispatachD inner join tbl_PckDetail on tbl_PckDetail.PID=tbl_SBDispatachD.PID inner join ItemMasterFile on ItemMasterFile.CodeItem=tbl_PckDetail.Codeitem inner join SalesOrder ON SalesOrder.OrderNo = tbl_PckDetail.OrderNo left join CustomerItem on CustomerItem.Codeitem=tbl_PckDetail.Codeitem and CustomerItem.CustomerID=SalesOrder.Customerid left outer join Probale on Probale.prdno=tbl_PckDetail.Prdno and ItemMasterFile.CodeItem=Probale.codeitem where tbl_SBDispatachD.ID =1109 and tbl_PckDetail.DelID is null and tbl_SBDispatachD.Del is null group by CustomerItem.CIName,ItemMasterFile.Descriptionitem ,Probale.Pbsize,ItemMasterFile.weight ,ItemMasterFile.BaleSize) ) select Descriptionitem, SUM (QY) as QY, SUM (WT) as WT, SUM (Total) as Total, MAX (X), SUM (Kg) as Kg from CTE group by Descriptionitem