ORDER BY IN VIEW

[dbomrrsm]

I have a view that returns customers name and address details based on input of the postcode/zipcode being LIKE blah%

Is there any way that I can order the results by the postcode/zipcode field.

When I check the syntax of the view with the ORDER BY clause included it says I cant use order by in a view unless TOP is also specified - I cant specify TOP as I never know how many records are to be returned.

Many thanks for any input.

DBomrrsm

 

[leonjollans]

you can't order a view. a view is a filtered rowset. if you need to order the data, you should order your select * from my_view statement instead. If you want it automatically ordered, you might consider building appropriate indexes on the underlying table instead. Even if doing this doesn't directly address your needs, acquiring sorted data will be a lot quicker.

in a sense its no different an idea from having an order by in your CREATE TABLE ddl statement. It doesn't make sense there either.

The reason you can order for a SELECT TOP is obviously that the top 10 rows differ depending on the sorting criteria.

hope this helps

Leon

 

[dbomrrsm]

Leon

I have tried to "order your select * from my_view statement"

but when I put the order by at the end of the select statement it said this couldnt be done for the above reasons.

view is something like

CREATE VIEW
AS
SELECT Name,Address,Postcode
FROM Tables
ORDER BY Postcode

This gives the error message Order By clause is invalid in views, inline functions, derived tables and subqueries, unless TOP is also specified.

You seem to think that it should work in the view statement - any ideas why it wont for me ?

cheers

DBomrrsm

 

[RobertT687]

You could try TOP 100 PERCENT in the select clause.

 

[swampBoogie]

You seem to think that it should work in the view statement - any ideas why it wont for me ?

That is not what he is saying, rather it is the opposite. The order by should be in the select statement, not the create view statement.

CREATE VIEW nap
AS
SELECT Name,Address,Postcode
FROM Tables

select * from nap order by postcode

 

[dbomrrsm]

tried TOP 100 PERCENT in the select clause - still doesnt order them

 

[nigelrivett]

You can use the top 100 percent in the view

select top 100 percent ...
from ...
order by ....

This will then have to be used in a query to return a result set thouhg and if that query doesn't have te same oreder by clause thre is no guarantee that the order will be preserved.
You should have an order by clause on the query that selects from the view. This is why it is not allowed in normal circumstances - for your owm protection.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.