Menu
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.
664
A view cannot be sorted with an ORDER BY clause. You need to put the ORDER BY clause into any query that references the view. Results of queries are ordered for display in the client application; rows in views and tables are unordered.
The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses.
Because a view is by definition an unordered set of rows. The only purpose of an ORDER BY in a view is in combination with TOP in order to determine which subset of the qualifying rows is selected.
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
You can try:
CREATE VIEW View_Products
AS
SELECT ProductID, ProductName, UnitPrice, CreateDate FROM Products
Order by CreateDate DESC
OFFSET 0 ROWS
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With