SQL Server Portal

In this article we will discuss a new error message (Window frame with ROWS or RANGE must have an ORDER BY clause) introduced in SQL SERVER 2012.

Let’s discuss it in detail:

Message Number: 10756

Severity : 15

Error Message: “Window frame with ROWS or RANGE must have an ORDER BY clause.”

Error Generation:
Let me create an example to generate this error:

USE AdventureWorks2012
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER (PARTITION BY SalesOrderID
--ORDER BY SalesOrderID, ProductID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS 'total'
FROM Sales.SalesOrderDetail
Where SalesOrderID IN(43659,43664)
Order By SalesOrderID

--OUTPUT

Msg 10756, Level 15, State 1, Line 2
Window frame with ROWS or RANGE must have an ORDER BY clause.

Resolution:
In the above example, you can see, I used Window frame with ROWS in the OVER clause but forgot to provide order by clause. Remember, whenever you use Window frame with rows or range, make sure you use it along with order by clause to avoid this error.

Please let me know if you have other samples to regenerate such error.