How to Create SQL View with Order By Clause
SQL Server The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. error occurs when database developer tries to use Order By clause in the definition of a SQL view.
This SQL tutorial shows how to create SQL views with Order By clause using TOP 100 Percent or Offset 0 Rows for Transact-SQL programmers and SQL Server database administrators.
SQL Server data professionals know that a SQL view does not provide an ordered list of data rows just like database tables. If you want to display or fetch data in an order, the SQL developer has to explicitly apply the ORDER BY clause on the result set.
It is better to explain this with a sample SQL view object. And then see if we can order a SQL View object as a SQL developer and SQL Server database administrator
Let's assume that our SQL developer wants to create a SQL Server View on SQL Server 2014 AdventureWorks sample database. Database programmer wants to retrieve department names in an alphabetically ordered list using the SQL Server view object.
create view Department_View
as
select Name from [HumanResources].[Department] order by Name
SQL Engine will display the following error message
Msg 1033, Level 15, State 1, Procedure Department_View, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
The best approach to create SQL view object and order data rows according to name field is as follows: First create SQL View without Order By clause. Then apply Order By in Select statement over SQL view data
create view Department_View
as
select Name from [HumanResources].[Department]
go
select * from Department_View order by Name
This method is the best way to sort data rows of an SQL View object using Order By clause on an SQL Server database
But as I had also required some SQL views to be sorted by default.
In such cases, as the error message "Msg 1033" indicates, ORDER BY clause can only be used in SQL views, inline functions, derived tables, subqueries and common table expressions (CTEs) if TOP, OFFSET or FOR XML is used with the Order By statement.
We will not use the FOR XML within a SQL View object.
But in order to retrieve SQL view data default sorted according to an Order By clause, we can use TOP or OFFSET keywords in our Create View statetments.
Order By Clause in SQL View using SELECT TOP 100 Percent
Let's continue our SQL tutorial showing how to order SQL view rows using Order By and TOP.
Here is the sample SQL View create syntax where TOP 100 Percent is used with Order By clause
create view Department_View
as
select top 100 Percent Name from [HumanResources].[Department]
order by Name
Even on a SQL2K, SQL Server 2005 or SQL Server 2008 and later database server instances, developers can use TOP with Order By in SQL view objects.
Order By in SQL View with Offset 0 Rows
An other option which is available for SQL programmers after the SQL Server 2012 release is using OFFSET in ORDER BY clause.
Below is an other method that I want to share with developers on ordering data rows in a database view object.
create view Department_View
as
select Name from [HumanResources].[Department]
order by Name offset 0 rows
Select statement returns all rows excluding the first n number of rows indicated after the Offset key
Of course if we set the offset rows number to 0, the SELECT statement will return all data set without excluding any records.
Since using Offset is valid with version SQL Server 2012 and later, you can not use it on previous version of SQL Server database instances.
Normally, SQL developers use the SELECT with Offset n Rows Fetch Next m Rows Only syntax as a SQL paging solution on SQL Server databases
As a result, I hope I could provide sample SQL view codes using "Select Top 100 Percent" and "Order By fieldname Offset 0 Rows" for database developers who have to use Order By clause and want to overcome the following SQL Server Engine error.
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.