Collections:
Using ORDER BY to Define a View in SQL Server
Can You Use ORDER BY When Defining a View in SQL Server?
✍: FYIcenter.com
Sometimes you want the data in a view to be sorted and try to use the ORDER BY clause in the SELECT statement to define the view.
But SQL Server will not allow you to use ORDER BY to define a view without the TOP clause. The tutorial exercise below shows you what error you will get when using ORDER BY in a CREATE VIEW statement:
USE FyiCenterData; GO CREATE VIEW fyi_links_top AS SELECT id, counts, url FROM fyi_links WHERE counts > 100 ORDER BY counts DESC; GO Msg 1033, Level 15, State 1, Procedure fyi_links_top, Line 4 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. CREATE VIEW fyi_links_top AS SELECT TOP 100 id, counts, url FROM fyi_links WHERE counts > 100 ORDER BY counts DESC; GO SELECT TOP 3 * FROM fyi_links_top; GO id counts url ------ ------- ------------------------------------------- 36470 999966 dgqnvmy pyjqd toqcoupuxortasdtzvcae jonfb 12292 999953 qebmw v qqmywe q kza wskxqns jnb 6192 999943 p o qisvrakk hk od
The view seems to be sorted correctly.
⇒ ALTER VIEW - Modifying Existing Views in SQL Server
⇐ Deleting a Table That Is Used by a View in SQL Server
2016-11-04, 1719🔥, 0💬
Popular Posts:
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...
How To Convert Characters to Numbers in Oracle? You can convert characters to numbers by using the T...
How To Convert Numeric Expression Data Types using the CONVERT() Function in SQL Server Transact-SQL...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
What Is a Dynamic Performance View in Oracle? Oracle contains a set of underlying views that are mai...