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
garystarkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

chapmandewCommented:
Please attach your view. Did you use TOP 100 PERCENT at the top of your view?
YiogiCommented:
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
chapmandewCommented:
Yiogi,
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

Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

garystarkAuthor Commented:
chapmandew, here are the screens.  As you can see I alreadyd have the Top 100 Percent in the definition.

gary
view.jpg
OpenView.jpg
YiogiCommented:
chapmandew that is the same solution as the article I included :)


YiogiCommented:
And gary you might want to add a WITH TIES to your query.
garystarkAuthor Commented:
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
garystarkAuthor Commented:
Ok, I see where the "with ties" goes.  I tried it...no difference.

gary
YiogiCommented:
basically you need with ties not for the sorting problem but so it won't eliminate dublicate records as TOP 100 percent would do.
garystarkAuthor Commented:
Yiogi, good point, but of course my sorting problem is unresolved.

gary
YiogiCommented:
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.
chapmandewCommented:
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?
Anthony PerkinsCommented:
Do not use an ORDER BY clause in your Views.  Period.
garystarkAuthor Commented:
acperkins, can you elaborate?

gary
maradamCommented:
In theory view is a virtual table. So it has no specyfic order. If you need your results ordered add order by clause out of the view. Sometimes however you "MUST" use order by inside a view because database software you use allow you for specyfying the view name but not order by clause.

There is, some workaround to this problem. If also specyfy TOP clause ORDER BY starts working as a filter, because the order determines which row will be returned or not. But how to return all rows and sill maintain the order? The answer is (or was) TOP 100 PERCENT. Top is added so you can also use order by. And it worked well in SQL 2000. Now with SQL 2005 guys from MS realized that TOP 100 PERCENT is not really a filter. So they allow for syntax, but eliminate both TOP and ORDER BY from operators from execution plans. However there are still some workarounds to this "fix".

1. Instead of using TOP 100 PERCENT, use TOP 99.999999999999 PERCENT, or TOP 100000000000000.
Of course there is a possibility to return not all rows, but the table should have laaaarge number of rows. So it works in majority of cases. What is more, MS himself uses this workaround in Migration Assistant from Access to SQL Server when converting access queries to views :)

2. Add a ranking function like row number and specify desired order in over() clause. Because SQL Server have to sort the table to compute row_number, sorted resultset is the easyiest to return. But remember: you MUST SPECIFY rownumber column in select clause and it must be the last column computed by ranking function returned.

WARINING! Views with order by work well if you use them directly from your application for simple selecting data. NEVER try to join them with other tables/views, because their performance is often horrible then.
-- first workaround
GO
create view dbo.vPersonContactOrdered
as
select top 99.99999999999 percent * from Person.Contact
order by firstname, lastname
GO
select * from dbo.vPersonContactOrdered -- it works
GO
create view dbo.vPersonContactOrdered2
as
select *, row_number() over (order by firstname, lastname) as rownumber from Person.Contact
GO
select * from dbo.vPersonContactOrdered2 -- it also works
GO

Open in new window

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
Anthony PerkinsCommented:
>>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.
ursangelCommented:
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>
ursangelCommented:
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>
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 2005

From novice to tech pro — start learning today.