How to sort within a sql view

Question

I've created a sql view and I need to sort the results of select by using the ORDER BY on 4 fields, but I'm getting message that ORDER BY cannot be used in views unless I use TOP. Can someone explain why TOP is needed, and does someone have a workaround for sorting in a sql view?

Thanks.

Erskine

Answer 1

you don't need to sort a view. a view is like a table so you sort it when you select from it:

select * from yourView order by yourColumns

Stockton

Answer 2

There is no guarantee the output of the view will be ordered

Only the outermost ORDER BY applies for result sets: not any inner ones. So only this ORDER BY can be guaranteed to work:

SELECT col1, col2, FROm MyView ORDER BY col2

You can add it to views or derived tables and it forces "intermediate materialisation" because the results have to be ordered. However, for SQL Server 2005 and above, you have to use TOP 2000000000 not TOP 100 PERCENT (except for that HF that Daniel Vassallo mentioned!)

Someone will use your view with a different order to that internally at some point too.

Dorene

Answer 3

You can try:

CREATE VIEW View_Products
AS
SELECT ProductID, ProductName, UnitPrice, CreateDate FROM Products
Order by CreateDate DESC
OFFSET 0 ROWS

Umbrageous

Answer 4

Would creating an index on the column with which you intend to sort the view help?

Macroclimate

Answer 5

In SQL Server, when you use the ORDER BY clause in a view, you gotta also use the TOP clause. That's because SQL Server needs a way to keep track of which rows go where when it shows you the results.

Now, there's a trick you can use with something called a Common Table Expression (CTE). In our case, we make a CTE that gives each row a number based on the order we want (in this case, the "Typ" field). Then, we tell the main part of our SQL code to look at that CTE, but only choose the rows where the number isn't null.

This lets us sort our rows in the view without using the ORDER BY clause directly. Instead, we're using the ROW_NUMBER() function in the CTE to get the sorting done

CREATE VIEW Vw_YourView
AS
WITH CTE AS (
SELECT guid, 
       Typ, 
       ROW_NUMBER() OVER (ORDER BY Typ) AS RowNum
FROM tbL_YourTable)
SELECT guid, 
       Typ
FROM 
       CTE
WHERE 
       RowNum IS NOT NULL;

Moth

For me the posted question is lacking detail. SQL Server is not the only DBMS that supports TOP. Also not clear (to me) whether the question is asking about the SELECT used to create the view or a separate SELECT performed against the created view. Hence I don't understand how the question got ten up-votes nor how any of the other answers got up-votes. For me the answers are just as vague as the question. Therefore I would say that your answer fits in well with all the other answers. – Agglutinin