· · (0) · ·

'Order by', in SQL Views :

SQL Server usually doesn't allow an ORDER BY clause in views. However, you can use this workaround: Just add a TOP 100 PERCENT clause to the front of the SELECT list.

Here's an example of how to use TOP 100 PERCENT to order data in views. The Northwind database contains a view erroneously called Alphabetical list of products. If you open this view, you'll see that it doesn't show the products in alphabetical order.

To put the products in alphabetical order, you modify the SQL code as follows:

SELECT TOP 100 PERCENT Products.*,
Categories.CategoryName AS CategoryName
FROM Categories INNER JOIN
Products ON
Categories.CategoryID =
Products.CategoryID
WHERE (Products.Discontinued = 0)
ORDER BY ProductName

note: some sql syntax checkers may complain. you can safetly ignore "ORDER BY not allowed in this type of query" warnings in this case. it should still work, but test it out first to make sure.