[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Microsoft, SQL Server, 2005, view with order by clause

Posted on 2007-11-18
10
Medium Priority
?
1,457 Views
Last Modified: 2012-06-27
Hi,
I have crated a view with Top 100 Percent in the Select Clause and an order by Column name. It is working fine when I execute the view in the Enterprise Manager.

But When I put,

Select * from View1

It is not giving the sorted result.
0
Comment
Question by:batchakamal
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20310889
this works right?
Select * from View1 order by columnname
i think top 100% order by x simply tells it to collect: up to 100%, by order of column x.  it can do that using multiple threads and arrive at some result. but because it's a view, it's the the final result. the result gets dumped in any order (that threads complete)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20310908
in views, you need to use the following method to allow sortings:


CREATE VIEW view_name 
AS 
SELECT TOP 100 PERCENT ... 
FROM ... 
WHERE ... 
ORDER BY ...

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20310910
sorry, I notice you did that :(

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20310912
how many CPU does your server have?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20310971
Check whether there exists another view with the same name and different owner  ?
0
 
LVL 4

Author Comment

by:batchakamal
ID: 20311067
We have Only one processor
0
 
LVL 4

Author Comment

by:batchakamal
ID: 20311073
If I put

Select * From View1 Order By Column2

It is working fine. But I require it to sort when I select.
0
 
LVL 4

Author Comment

by:batchakamal
ID: 20311088
There is only one view with that name.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 1500 total points
ID: 20311109
well in that case,
select top 100000000 *
from table
order by col1
0
 
LVL 4

Author Closing Comment

by:batchakamal
ID: 31409897
It Works out. But I would like to know whether this will affect our performance or not.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 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