ORDER BY clause is invalid - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ORDER BY clause is invalid

Author

Topic

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-20 : 18:18:55

Hi guys,
Im getting this error message on this code below, i have looked it up on http://support.microsoft.com/kb/841845, but im not sure i understand it. could someone please explain this to me, and how to fix it as im getting this error not only on views but also on nested queries.
Cheers

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

The code is:

create view PGT as
(
select Product_Code, '$' + convert(varchar(25),sum(amount + GST_amount),1) as Product_Group_Totals
from commission_txn
group by Product_Code
order by Product_Code
)

cheers
GK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-20 : 18:30:52

you can't use ORDER BY in creating a view except with TOP command

Remove the ORDER BY


create view PGT as
(
select Product_Code, '$' + convert(varchar(25),sum(amount + GST_amount),1) as Product_Group_Totals
from commission_txn
group by Product_Code
order by Product_Code
)


create view PGT as
(
select TOP 100 PERCENT Product_Code, '$' + convert(varchar(25),sum(amount + GST_amount),1) as Product_Group_Totals
from commission_txn
group by Product_Code
order by Product_Code
)

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-20 : 18:38:58

you can't use ORDER BY in creating a view except with TOP command.
So the Top 100 percent will cater for the order by?
GK

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-20 : 18:45:04

Correct, but don't go putting TOP 100 PERCENT everywhere. Put the ORDER BY in the queries that use the view or for a subquery

SELECT A.column1, A.column2
FROM (subquery) A
ORDER BY A.column1

If you put the ORDER BY in the view or subquery you may well be making SQL Server sort the data more than once and you'll get poor performance as a result.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-20 : 18:51:14

also, the optimizer reserves the right to ignore an order by clause if it's in a view definition in its quest to find more efficient plans. so don't do it. put the order by clause in the query that targets the view, as snSQL says.

see:
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-20 : 19:14:04

sorry, some of my posts may seem quite trivial to you guys.
Cheers Guys makes sense and works perfect now.
GK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources