Browse DevX
Sign up for e-mail newsletters from DevX


Make SQL Server Respond to an ORDER BY Clause : Page 2

SQL Server Views don't support the ORDER BY clause, but that shouldn't stop you from bringing order to your data. This article teaches you how to sort the results of a View, despite this small limitation.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 datasource ORDER BY sortcolumn [ASC | DESC]

Figure 1  
Figure 1. The data doesn't sort. (Click to enlarge)

Where column identifies the columns you're retrieving, datasource identifies the tables that contain the data, and sortcolumn is the column by which you want to sort the results. By default, the clause sorts in ascending order, but you can sort in ascending or descending order using ASC or DESC. (For documentation purposes, you might want to include ASC, but it isn't necessary.) You can sort a text, numeric, or date column.

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  
Figure 2. The ORDER BY clause will sort the results. (Click to enlarge)

Where column identifies the columns you're retrieving, viewname identifies the View from which you're pulling those columns, criteria is an expression that limits the data, and sortcolumn identifies the column by which you want to sort the results. As you can see, in this form, you can include an ORDER BY clause, but you're doing so indirectly—from the calling SELECT statement, which may not always be adequate.

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.viewProducts AS SELECT dbo.Products.ProductName, dbo.Products.CategoryID FROM 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.CategoryID FROM dbo.viewProducts ORDER 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).

Thanks for your registration, follow us on our social networks to keep up-to-date