Solved

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

Posted on 2007-11-18
10
1,445 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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