?
Solved

view are not sorting in SqlServer 2005??

Posted on 2006-04-30
12
Medium Priority
?
244 Views
Last Modified: 2008-02-01
For some reason my views are no longer sorting in SqlServer 2005.

I migrated my database from SqlServer 2000 to SqlServer 2005 and now my sort by clause in all my views no longer work. Why is this, and is there a change that I can make to restore sorting in views.


For example, this view does not return the results sorted by name, when before in SqlServer 2000, it did.

create view myView
select top 100 percent name, id  from mytable
order by name



0
Comment
Question by:brokeMyLegBiking
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 16574450
interesting answer!
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 16574544
golly that got all the entire page ....strange.....very interesting how that happened.
0
 
LVL 4

Author Comment

by:brokeMyLegBiking
ID: 16574566
what where you trying to say?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 16574577
strange problem, never observed.
instead of top 100 percent if we use top 100 its working. but we need all the records so i tried the following it works.

try as follows

create view myView
as
select * from (select top 100 percent name, id  from mytable
order by name) as A

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16574893
This kludge (adding 100 PERCENT to a VIEW in order to be able to use ORDER BY) still works for me with SQL Server 2005 (Compatibility Level = 90).  However, I would personally never use it as you are essentially limiting the usefulness of the VIEW.  In other words, there should not ne any need whatsoever to use an ORDER BY clause on a VIEW.  The correct way to use a VIEW is as follows:

create view myView

As

select name, id  from mytable


And then call it as follows:
Select * from myView ORDER BY name

Or:
Select * from myView ORDER BY id


0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16576039
try using asc
========
create view myView as
select top 100 percent name, id  from mytable
order by name asc
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16576637
I could reproduce the same behaviour with the RC2, not sure if in the final release it has been solved...
0
 
LVL 4

Author Comment

by:brokeMyLegBiking
ID: 16576774

imran_fast, that didn't work.
acperkins, it does not work for me. How did you get it to work, and yes, I do need an ORDER BY in a view. (I realize the performance implications and they are acceptable, given my application architecture)


appari, your suggestion works. Using an absolute number instead of a percent works. So if I just put a really really big number, then my view always sorts successfully:

alter view testJoe as select top 1000000000  * from table1 order by expense
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16582461
>> I do need an ORDER BY in a view. <<
You are missing the point.

Good luck.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16586538
wht if you remove percent is it working

create view myView as
select top 100  name, id  from mytable
order by name asc
0
 
LVL 4

Author Comment

by:brokeMyLegBiking
ID: 16594614
>> no I'm not, you are assuming the usually best way as recommended by the textbooks holds in EVERY case<<

I have an application which dynamically builds sql statements. I want to put as much of the tsql in my databse as possible, rather than in each of the various client applications I have accessing the view. To put the order by clause in the client application is not nearly as elegant as putting it in the view.

imran_fast, your query only returns the top 100 results, if the query has more results than 100, the remaining will not be returned.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question