Can You Use ORDER BY When Defining a View?
Submitted by: AdministratorSometimes 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 GlobalGuideLineDatabase;
GO
CREATE VIEW ggl_links_top AS
SELECT id, counts, url FROM ggl_links
WHERE counts > 100
ORDER BY counts DESC;
GO
Msg 1033, Level 15, State 1, Procedure ggl_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 ggl_links_top AS
SELECT TOP 100 id, counts, url FROM ggl_links
WHERE counts > 100
ORDER BY counts DESC;
GO
SELECT TOP 3 * FROM ggl_links_top;
GO
<pre>id counts url
------ ------- -------------------------------------------
36470 999966 dgqnvmy onfb
12292 999953 qebmw v qkxqns jnb
6192 999943 p o qik od</pre>
The view seems to be sorted correctly.
Submitted by: Administrator
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 GlobalGuideLineDatabase;
GO
CREATE VIEW ggl_links_top AS
SELECT id, counts, url FROM ggl_links
WHERE counts > 100
ORDER BY counts DESC;
GO
Msg 1033, Level 15, State 1, Procedure ggl_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 ggl_links_top AS
SELECT TOP 100 id, counts, url FROM ggl_links
WHERE counts > 100
ORDER BY counts DESC;
GO
SELECT TOP 3 * FROM ggl_links_top;
GO
<pre>id counts url
------ ------- -------------------------------------------
36470 999966 dgqnvmy onfb
12292 999953 qebmw v qkxqns jnb
6192 999943 p o qik od</pre>
The view seems to be sorted correctly.
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Convert Binary Strings into Integers in MS SQL Server? |
☺ | What Happens If NULL Values Are Involved in Bitwise Operations? |
☺ | What Happens If You Insert a Duplicate Key for the Primary Key Column in MS SQL Server? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | How To Use Subqueries with the IN Operators in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |