sorting in views
I created a view in the AdventureWorks db, showing the contact names. I then specified an ascending sort on the name column. All works exactly as expected.
Now I save and close the view. I then "Open View" to once again see the output. The names are no longer sorted properly, as if it's completely ignoring my sort setting. What's going on???
gary
Now I save and close the view. I then "Open View" to once again see the output. The names are no longer sorted properly, as if it's completely ignoring my sort setting. What's going on???
gary
chapmandew
Please attach your view. Did you use TOP 100 PERCENT at the top of your view?
SQL Server views do not support ORDER BY.
You are supposed to sort when you select from the view and not in the view itself.
Also have a look at this though:
http://www.devx.com/dbzone/Article/8048
You are supposed to sort when you select from the view and not in the view itself.
Also have a look at this though:
http://www.devx.com/dbzone/Article/8048
Yiogi,
That is not correct. You can absolutely sort in Views...you just have to specify TOP 100 PERCENT at the beginning.
That is not correct. You can absolutely sort in Views...you just have to specify TOP 100 PERCENT at the beginning.
CREATE VIEW vw_test
AS
SELECT TOP 100 PERCENT * FROM Table
ORDER BY SSN
ASKER
chapmandew, here are the screens. As you can see I alreadyd have the Top 100 Percent in the definition.
gary
view.jpg
OpenView.jpg
gary
view.jpg
OpenView.jpg
chapmandew that is the same solution as the article I included :)
And gary you might want to add a WITH TIES to your query.
ASKER
Yiogi, that article was written in 2002. In looking at my view, it already have the "top 100 percent" statement (I don't have a choice...if I delete it, sql puts it back). Not sure what you mean by "WITH TIES". Was that in the article?
gary
gary
ASKER
Ok, I see where the "with ties" goes. I tried it...no difference.
gary
gary
basically you need with ties not for the sorting problem but so it won't eliminate dublicate records as TOP 100 percent would do.
ASKER
Yiogi, good point, but of course my sorting problem is unresolved.
gary
gary
Sorry gary I would like to help more but honestly I can't. It's 2am here and I have to wake up at 8 so I'll have to leave you guys. if you still have a problem tomorrow I'll try and simulate the error and see if I can think of anything else.
With Ties isn't going to help you. WITH TIES only really matters if you are returning a percentage less than 100 and you want to see all of the rows if their sorting criteria "ties" for the last few matches.
What happens when you just do a select * from view_test? Are the results returned differently than they are when you open the view in Management Studio?
What happens when you just do a select * from view_test? Are the results returned differently than they are when you open the view in Management Studio?
Do not use an ORDER BY clause in your Views. Period.
ASKER
acperkins, can you elaborate?
gary
gary
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>can you elaborate?<<
Sure, but maradam has made most of my case rather well if the thought of using TOP 99.999999999999 does not make you cringe than let me just add this:
Supposing that you had a Books table and you created a View against that and through some cheesy workaround that may end up deprecated in the next version of SQL Server managed to sort it by Book title. Now you decide you want a similar extract but this time sorted by Author, so you happily copy the first view and change the ORDER BY. later on you need one based on ISBN so you go ahead and ... Well you get the picture. Now the fun starts: You have 3 views to maintain, every time a new column is added or removed, a condition is changed you have to remember to maintain 3 different places. What a nightmare. The alternative is one View with three different ORDER BY clauses. When you are maintining somewhere in the region of 80 databases in three different environments, you don't look for shortcuts that will come up to haunt you.
Views are intended to extract data in a certain format, they were not designed for the purpose of changing the sort order. Unfortunately, too many MS Access developers "upgrade" their databases and their skills and look for "equivalents" of their comfort zone and think among other things that a View is just another name for an MS Access query and rant and rave when they realize that their are subtle differences.
This is also true, of the transition from SQL Server 2000 to 2005 (as it was from SQL Server 6.0 to 6.5 to 7.0 to 2000): Things change. Get used to it.
Sure, but maradam has made most of my case rather well if the thought of using TOP 99.999999999999 does not make you cringe than let me just add this:
Supposing that you had a Books table and you created a View against that and through some cheesy workaround that may end up deprecated in the next version of SQL Server managed to sort it by Book title. Now you decide you want a similar extract but this time sorted by Author, so you happily copy the first view and change the ORDER BY. later on you need one based on ISBN so you go ahead and ... Well you get the picture. Now the fun starts: You have 3 views to maintain, every time a new column is added or removed, a condition is changed you have to remember to maintain 3 different places. What a nightmare. The alternative is one View with three different ORDER BY clauses. When you are maintining somewhere in the region of 80 databases in three different environments, you don't look for shortcuts that will come up to haunt you.
Views are intended to extract data in a certain format, they were not designed for the purpose of changing the sort order. Unfortunately, too many MS Access developers "upgrade" their databases and their skills and look for "equivalents" of their comfort zone and think among other things that a View is just another name for an MS Access query and rant and rave when they realize that their are subtle differences.
This is also true, of the transition from SQL Server 2000 to 2005 (as it was from SQL Server 6.0 to 6.5 to 7.0 to 2000): Things change. Get used to it.
Order by is invalid in the view.
You will get this error message from the sql server itself.
'The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.'
better specify the TOP clause or user order by while selecting from the view
eg:- select * from <View Name>
You will get this error message from the sql server itself.
'The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.'
better specify the TOP clause or user order by while selecting from the view
eg:- select * from <View Name>
Order by is invalid in the view.
You will get this error message from the sql server itself.
'The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.'
better specify the TOP clause or user order by while selecting from the view
eg:- select * from <View Name>
You will get this error message from the sql server itself.
'The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.'
better specify the TOP clause or user order by while selecting from the view
eg:- select * from <View Name>