Browse DevX
Sign up for e-mail newsletters from DevX


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

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 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])] AS SELECT TOP 100 PERCENT column[,n] FROM datasource [WHERE criteria] [ORDER BY sortcolumn[,n]

Figure 3  
Figure 3. The View sorts the results. (Click to enlarge)

In the above context, the TOP predicate selects all the data—100 percent. You can use this predicate to select n percent or n records from the data source.

Following the previous example, you can add the ORDER BY clause to the View as follows:

CREATE VIEW dbo.viewProductsTop AS SELECT TOP 100 PERCENT dbo.Products.ProductName, dbo.Products.CategoryID FROM dbo.Products ORDER 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.CategoryID FROM dbo.viewProductsTop

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