ORDER BY clause within VIEW

Database
Enthusiast

ORDER BY clause within VIEW

HI ALL,
Can anybody explain me why the ORDER BY is not used within a view

7 REPLIES
Enthusiast

Re: ORDER BY clause within VIEW


The order by in a view has no meaning, since the select statement inside a view is not the final select.

Regards,
Balamurugan
Enthusiast

Re: ORDER BY clause within VIEW

I always thought ORDER BY is not allowed. However, looking at the syntax diagram in Chapter 3: SQL Data Definition Language Statement Syntax
CREATE VIEW REPLACE VIEW in SQL/data Dictionary quick reference, I was surprised that it says otherwise.

However, when we use ORDER BY , we get 3706 ERROR which says "SYNTAX ERROR : ORDER BY IS NOT ALLOWED IN SUBQUERIES". From this, looks like the select statement in View is treated as a subquery and that's why we get this error.

Any thought?
Enthusiast

Re: ORDER BY clause within VIEW

Thanks Murugan and Somesh
Enthusiast

Re: ORDER BY clause within VIEW

One can specify the Order by Clause only when we use the Top n Option in the select query to select the top n rows from the base table.
(This info is available in the SQL Data Definition PDF)

Regards,
Annal T
Enthusiast

Re: ORDER BY clause within VIEW

Order by is not used in view clause(DML),
I thought view is just a sql, and when you read data from view, if there is oder by , this will cause the data to redistribute, in fact , if you want the data to be ordered, you may use select * from view order by ...
so no need to use order by in view clause(DML)
Enthusiast

Re: ORDER BY clause within VIEW


To add one more point to above discussion, the ORDER BY is allowed in view when used along with the set opertors.

Regards,
Balamurugan

Re: ORDER BY clause within VIEW

We can't use ORDER BY clause in View defintion and in subquery.

As both are not the final result set.

Teradata is designed to perform in most optimized path.

If there is an option to put oder by in any of above cases, it means Teradata mean to order the intermediate result set. Which means results in SPOOL should be ordered. This will degrade the performance.

So in order to have maximum performance and best PE plan ORDER BY clause is not allowed in View definition and in Sub queries or any where which is NOT the final results.