ometimes, how you present data is just as important as the data itself. One of the simplest presentation techniques is to sort the data in some meaningful way, but SQL Server Views don’t support the ORDER BY clause. In this article, you’ll learn how to sort the results of a View, despite this small limitation.
About Views
The SQL Server View is one of SQL Server’s simpler structures for displaying data. A View basically retrieves a limited recordset. You can retrieve all the data, but you’ll rarely need to do so. Views also are one of SQL Server’s more flexible tools because:
The View does have a few restrictions, however:
To create a View, use the CREATE VIEW statement in the form (Arguments enclosed in brackets, [ ], are optional.):
CREATE VIEW [databasename.][owner.]viewname[(column[,n])][WITH attribute[,n]]ASselectstatement[WITH CHECK OPTION]
The WITH CHECK OPTION clause tracks changes to the data via the View to ensure that the View is still valid once the statement is executed. If a change invalidates the View, this option prohibits the change. Table 1 describes the CREATE VIEW arguments.
Table 1: CREATE VIEW Arguments
| Argument | Explanation |
| databasename | Identifies the database that contains the data you’re viewing |
| owner | Identifies the database owner, usually a three-letter prefix such as dbo |
| column | Identifies the columns from which you’re retrieving data (in selectstatement; generally used only if column is the result of an expression, function, or if two columns have the same name |
| attribute | One of the following:
|
| selectstatement | A valid SQL statement that fully defines the View |
The ORDER BY clause sorts the results of a SELECT statement. To apply a sort, add this clause in the form:
SELECT column[,n]FROM datasourceORDER BY sortcolumn [ASC | DESC]
 | ? |
| Figure 1. The data doesn’t sort. (Click to enlarge) |
The Simplest Solution
Using a View is as simple as creating a SELECT statement. That statement can even further restrict the View’s results, and it even offers a simple solution to the ORDER BY clause limitation because you can include the ORDER BY clause in the calling SELECT statement. To use a view, create a SELECT statement using the following syntax:
SELECT column[,n]FROM viewname[WHERE criteria]ORDER BY sortcolumn[,n]
 | ? |
| Figure 2. The ORDER BY clause will sort the results. (Click to enlarge) |
The Northwind sample database that comes with SQL Server can provide a quick example. Suppose you want to see all the products, sorted first by their categories and then by product name. To do so, you might create the following View:
CREATE VIEW dbo.viewProductsASSELECT dbo.Products.ProductName, dbo.Products.CategoryIDFROM dbo.Products
Executing a simple SELECT statement returns the data, but it doesn’t sort it (see Figure 1).
Call the View using a simple SELECT statement as follows:
SELECT dbo.viewProducts.ProductName, dbo.viewProducts.CategoryIDFROM dbo.viewProductsORDER BY dbo.viewProducts.CategoryID, dbo.viewProducts.ProductName
The results aren’t limited by a WHERE clause, and they will include all the records from both the CategoryID and ProductName columns. However, the ORDER BY clause will sort the results, first by CategoryID and then by ProductName (see Figure 2).The previous solution works, but you lose a little control over the sort because you may forget to include the sort when calling the View. A more direct approach is to add the TOP predicate to the View’s SELECT statement in the form:
CREATE VIEW [databasename.][owner.]viewname[(column[,n])]ASSELECT TOP 100 PERCENT column[,n]FROM datasource[WHERE criteria][ORDER BY sortcolumn[,n]
 | ? |
| Figure 3. The View sorts the results. (Click to enlarge) |
Following the previous example, you can add the ORDER BY clause to the View as follows:
CREATE VIEW dbo.viewProductsTop ASSELECT TOP 100 PERCENT dbo.Products.ProductName, dbo.Products.CategoryIDFROM dbo.ProductsORDER BY dbo.Products.CategoryID, dbo.Products.ProductName
The results show the View sorted by CategoryID and then ProductName (see Figure 3). When calling the View with the following statement, the ORDER BY clause in the calling SELECT statement is no longer needed:
SELECT dbo.viewProducts.ProductName, dbo.viewProducts.CategoryIDFROM dbo.viewProductsTop
There’s one problem with the TOP 100 PERCENT solution. As is, the View could drop a record from the results. When the last value has two or more records with the same value, the View includes only one. To avoid this problem, add the WITH TIES clause to the TOP statement in the form:
SELECT TOP 100 PERCENT WITH TIES
Circumvent the Rules
Rules are made to be broken. SQL Server doesn’t support the ORDER BY clause in a View, but you’ve seen how to get around that limitation by adding a TOP 100 PERCENT clause to the View. When you do, SQL Server interprets and correctly responds to an ORDER BY clause.
