Solved

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

Posted on 2007-11-18
10
1,444 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 142

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 142

Expert Comment

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

0
 
LVL 142

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now