SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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 Order By clause is invalid in views


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

sort SQL View data rows using Order By clause on 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

Order By clause with TOP 100 Percent in SQL Server View

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.

sort SQL Server View with Order By Offset 0 Rows

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.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems