Solved

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

Posted on 2007-11-18
10
1,446 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to check data in sql table 11 51
passing parameter in sql procedure 9 65
SQL Error - Query 6 40
Need help in debugging a UDF results 7 14
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 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