SQL Server view order by

by
SQL Server View ORDER BY

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.

SQL Server view order by doesn't work
SQL Server view order by doesn’t work

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.

  1. First, create a view without any ORDER BY clause.
  2. Second, use the SELECT statement to query the result of a view.
  3. 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.

SQL Server View ORDER BY
SQL Server View ORDER BY

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.

SQL Server view order by top 100 percent
SQL Server view order by top 100 percent

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.

SQL Server view order by offset
SQL Server view order by offset

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.

SQL Server view order by desc
SQL Server view order by desc

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.

SQL Server view order by desc example
SQL Server view order by desc example

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.

SQL Server function return table variable example-1
Customers_US table

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

SQL Server function return table variable example-2
Customers_CA table

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.

SQL Server view union order by
SQL Server view union order by

You may also like the following SQL server tutorials:

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