Avoid order by to use a sql in a view

Question

Post reply

Avoid order by to use a sql in a view

Juergen Gleiss

Mr or Mrs. 500

Points: 543
More actions
November 12, 2008 at 1:14 pm

#195947

Hi,
i have a sql which works:
select
[Customer No_],
sum(Amount) as Balance
from [Detailed Cust_ Ledg_ Entry]
group by
[Customer No_]
order by
[Customer No_]
when i want to use this sql in a view - i receive this error:
--> 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.
Does anybody know a work around for this - would be great 😀
Greetings form Austria!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 1

Give this a shot:

CREATE VIEW viewname AS

SELECT TOP 100 PERCENT

[Customer No_],

sum(Amount) as Balance

from [Detailed Cust_ Ledg_ Entry]

group by

[Customer No_]

order by

[Customer No_]

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

That is valid, but the order by will not be honoured. Order bys are only honoured by the query processor if they are in the outermost select statement, i.e. the one that selects from the view.

Define the view without the order by and then use the order by in the query that selects from the view.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ALI-408105 Say Hey Kid Points: 709 More actions · Answer 3

Here is more informaiton on this issue

http://cf-bill.blogspot.com/2007/01/sql-server-order-view.html]

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 4

Oh, thanks for reminding me... should have remembered that! I recall when we updated an application to SQL 2005, the users reported that their sorts were not honored in their reports (sort defined in the view)

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

Juergen Gleiss Mr or Mrs. 500 Points: 543 More actions · Answer 5

GilaMonster: Thank you for your quick answer

Ali: your link does not work - please update the working one.

Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Juergen Gleiss (11/13/2008)
Ali: your link does not work - please update the working one.

Try removing the square bracket from the end of the URL

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass