In this SQL Server tutorial, we will discuss the use of SQL Server View Order By. Here we will illustrate the use of the ORDER BY clause in a SQL Server View. Moreover, we will cover the following topics.
- SQL Server view order by
- SQL Server view order by top 100 percent
- SQL Server view order by desc
- SQL Server view order by offset
- SQL Server view union order by
- SQL Server view order by doesn’t work
SQL Server View ORDER BY
In SQL Server, a VIEW is just like a virtual table that holds data from one or more than one table. A view includes a set of SQL queries for retrieving data from the database. For more information refer to “View in SQL Server“.
On the other hand, the ORDER BY clause in SQL Server helps to arrange the resultset data either in ascending or descending order. Moreover, it allows to arrange resultset by the supplied column list and restrict the rows returned to a specific range.
Now, let’s take a look at the syntax of using an ORDER BY and creating a view in SQL Server.
/* Syntax of creating a view */
CREATE VIEW view_name AS
SELECT expressions
FROM table_name
[WHERE condition];
/* Syntax of using ORDER BY */
SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ASC|DESC];
Now, here comes an important question. Can we use the ORDER BY clause in a SQL SERVER View? However, the answer to this question is, NO, we cannot use the ORDER BY clause in a SQL SERVER View.
SQL Server view order by doesn’t work
As stated earlier, a view in SQL Server does not support the use of the ORDER BY clause. Moreover, even if try to use, the SQL Server will return an error.
Let’s understand the whole situation using an example in SQL Server. For this, we will create a view that includes the ORDER BY clause in its definition.
USE [sqlserverguides]
GO
CREATE VIEW [dbo].[USA_CustomerView]
AS
SELECT customer_name, city, country
FROM Customers
WHERE country = 'United States'
ORDER BY customer_name
GO
In the above example, we have created a view with the name USA_CustomerView. This view fetches the records from the Customers table where the customer country is the United States.
Moreover, we have also used the ORDER BY clause to order the result based upon the customer_name column. Now, when we execute this query, SQL Server will return the following error.

Also read, How to see view definition in SQL Server
SQL Server View ORDER BY : Solution
So, the best method to use a view and ORDER BY clause in SQL Server is as follows.
- First, create a view without any ORDER BY clause.
- Second, use the SELECT statement to query the result of a view.
- In the end, use the ORDER BY clause while querying the view.
Now, let’s use this method to use a view and ORDER BY together. For this, consider the following SQL query.
USE [sqlserverguides]
GO
CREATE VIEW [dbo].[USA_CustomerView]
AS
SELECT customer_name, city, country
FROM Customers
WHERE country = 'United States'
GO
SELECT * FROM [dbo].[USA_CustomerView]
ORDER BY customer_name
Now, this time, after execution, we will get the following result. Moreover, the result is also in ascending order.

Read Comparison Operators in SQL Server
SQL Server view order by top 100 percent
Now, there is another way to resolve the error caused by using the ORDER BY clause in a view. And the solution includes the use of the TOP 100 PERCENT keyword.
The TOP keyword in SQL Server indicates the number of rows that a SELECT statement should return. While the PERCENT keyword indicates the percentages of rows that the query should return.
Let’s take an example and understand how this keyword will help to remove the error.
USE [sqlserverguides]
GO
ALTER VIEW [dbo].[USA_CustomerView]
AS
SELECT TOP 100 PERCENT id, customer_name, city, country
FROM Customers
WHERE country = 'United States'
ORDER BY customer_name
GO
In the above example, we have modified the USA_CustomerView view. And this time, we are using the TOP 100 PERCENT keyword with the SELECT statement. Moreover, wear also using the ORDER BY clause in the end.
But, here comes a twist, this keyword only helps to remove the error and the result will not be in the specified order. For better understanding, take a look at the output of the view.

Also read, How to delete a view in SQL Server
SQL Server view order by offset
Another possible solution to use the ORDER BY clause in a view is by using the OFFSET clause.
In SQL Server, the FETCH and OFFSET clauses were first introduced in SQL Server 2012. And these clauses are utilized with the SELECT and ORDER BY clauses to restrict the number of records returned by a query.
The OFFSET clause defines the number of rows to be ignored before returning records from the query. Whereas the FETCH clause indicates how many rows should be returned after ignoring.
Moreover, the OFFSET clause is mandatory to use, while the FETCH clause is optional. Here is the syntax of using these clauses in SQL Server.
SELECT expressions
ORDER BY expressions[ASC |DESC]
OFFSET offset_row_count {ROW | ROWS}
FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY
In the above syntax, offset_row_count is used to define the number of rows for the OFFSET clause. And fetch_row_count is used to define the number of rows for the FETCH clause.
Now that we got a clear idea about the OFFSET clause in SQL Server, let’s understand how to use it with ORDER BY in a view. For this task, we are implementing the following query.
USE [sqlserverguides]
GO
ALTER VIEW [dbo].[USA_CustomerView]
AS
SELECT id, customer_name, city, country
FROM Customers
WHERE country = 'United States'
ORDER BY customer_name
OFFSET 1 ROWS
GO
SELECT * FROM [dbo].[USA_CustomerView]
In the above example, we are modifying an existing view and adding the ORDER BY clause in the definition. After this, we have also specified the OFFSET clause with a parameter as 1. So, it will ignore the first row and return the remaining ones in order. Here is the output for this example.

Read SQL Server bulk insert from CSV file
SQL Server view order by desc
Till now, we have ordered the view data in ascending order in SQL Server. But, the ORDER BY clause also enables ordering data in descending order.
So, next, let’s understand how to order the view data in descending order in SQL Server. Moreover, we will also illustrate multiple ways to achieve this task.
Method-1: Using OFFSET
In the previous section, we have seen how to use the OFFSET and ORDER BY clause together. In the same way, we can use the OFFSET and ORDER BY clause together to order the data of a view in descending order.
Additionally, we just need to use the DESC keyword with the ORDER BY clause and specify the offset as zero(0). The example for this implementation is shown below.
USE [sqlserverguides]
GO
ALTER VIEW [dbo].[USA_CustomerView]
AS
SELECT id, customer_name, city, country
FROM Customers
WHERE country = 'United States'
ORDER BY customer_name DESC
OFFSET 0 ROWS
GO
- In the above example, first, we are creating a view that fetches the data of customers who are from the United States.
- After this, we are using the ORDER BY clause to order the records based upon customer names.
- Moreover, we have used the DESC keyword so, the records will be in descending order.
- In the end, we are using the OFFSET clause with 0 rows so, it will not skip any row.
Now, if we query the view, we will get the following result.

Read SQL Server function return table
Method-2: Using ORDER BY outside of view
The best way to implement this task is by using ORDER BY and a view separately. Let’s understand this implementation using an example in SQL Server.
Therefore, for the example, first, we will create a simple view in SQL Server. And query to create a view is as follows.
USE [sqlserverguides]
GO
ALTER VIEW [dbo].[USA_CustomerView]
AS
SELECT id, customer_name, city, country
FROM Customers
WHERE country = 'United States'
GO
After creating a view, it’s time to use the SELECT statement to fetch data from a view. And at this time, we can use the ORDER BY clause to arrange data in descending order.
USE [sqlserverguides]
GO
SELECT * FROM [dbo].[USA_CustomerView]
ORDER BY customer_name DESC
In the end, we will get our required result in descending order.

Read Arithmetic operators in SQL Server
SQL Server view union order by
In this section, we will understand how to use the ORDER BY clause and UNION operator together in a SQL Server View. But, before this, we need to understand the use of the UNION operator in SQL Server.
The UNION operator in SQL Server helps to concatenate results from 2 queries. Moreover, we even have an option to include duplicate records or not. On the other hand, the ORDER BY clause in SQL Server helps to arrange data either in ascending or descending order.
Now, we can easily use the UNION operator in a view to combining the results from multiple tables or queries. But, a view does not support the use of the ORDER BY clause. So, for this implementation, we also have to use the OFFSET clause.
Example
Let’s understand this implementation using an example in SQL Server. And for this, first, consider the following tables.

Customers_US table consists of data of customers who are from the United States

Customers_CA table consists of data of customers who are from Canada
Next, we will create a view that consists of data from both tables. Moreover, we will also use the ORDER By clause to order data. Here is the query for the example.
USE [sqlserverguides]
GO
CREATE VIEW [dbo].[AllCustomers]
AS
SELECT id, customer_name, city, 'United States' Country
FROM dbo.Customers_US
UNION
SELECT id, customer_name, city, 'Canada' Country
FROM dbo.Customers_CA
ORDER BY customer_name
OFFSET 0 ROWS
In the above, we have used two queries within the view. The first query is fetching data from the Customers_US table and the second query fetches data from the Customers_CA table. In between these queries, we have specified the UNION operator. And this will combine the records from both queries in one result set.
In the end, we are using the ORDER BY clause, so, it will take one record from each query and arrange them in ascending order. Here is the final output.

You may also like the following SQL server tutorials:
- SQL Operand data type real is invalid for modulo operator
- How to test stored procedure in SQL Server
- Alter view in SQL Server
- SQL Server scheduled stored procedure
- SQL Server stored procedure case statement
- SQL Server First Day Of Month
- How to view table in SQL Server
So, in this tutorial, we have discussed the use of the ORDER BY clause in a SQL Server View. Moreover, we have covered the following topics.
- SQL Server view order by
- SQL Server view order by top 100 percent
- SQL Server view order by desc
- SQL Server view order by offset
- SQL Server view union order by
- SQL Server view order by doesn’t work
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.