Link to home
Avatar of Ernesto
ErnestoFlag for Mexico

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Ernesto

ASKER

but the top 100 think limit me to 100 records?
there are few more
tsm
TOP 100 limits to 100 rows, TOP 100 PERCENT effectively has no limit.
This is not an abundantly intuitive workaround I know..
Avatar of Ernesto

ASKER

Oh man
tsm
regards
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.