generate a view

hi all how do i generate this view

create view tubos as
select min(size_1),max(size_1), long_desc2+' '+long_desc1,face,cedula_lb from pipe_pipe_spec group by long_desc2,long_Desc1,face,cedula_lb order by long_Desc2

error
La cláusula ORDER BY no es válida en vistas, funciones en línea, tablas derivadas ni subconsultas, salvo que se especifique también TOP.
the order clause is not valid in views functions on line, derrivated tables nor subconsults, even is specificated also TOP
ErnestoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT TOP 100 PERCENT 
blah blah blah
ORDER BY long_Desc2

Open in new window

There's some quirky dang deal with SQL views where it doesn't allow ORDER BY without a TOP.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ErnestoAuthor Commented:
but the top 100 think limit me to 100 records?
there are few more
tsm
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
TOP 100 limits to 100 rows, TOP 100 PERCENT effectively has no limit.
This is not an abundantly intuitive workaround I know..
0
ErnestoAuthor Commented:
Oh man
tsm
regards
0
PortletPaulfreelancerCommented:
The reason for this "quirky" stuff is that YOU SHOULD NOT ORDER VIEWS.

Imagine a situation where you are using 2 or more views in a query, each of those views has been ordered, but the FINAL presentation requires it's own particular order. No problem just add an ORDER BY clause

HOWEVER now your server has wasted effort. The ordering of views leads to wasted effort and you should use an order by at the FINAL presentation.

So, create you view without the order, then select from the  view and order that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.