(B)Need Help in SQL View!(/B)

[emilyng]

The following is my statement that I put in SQL View :

SELECT DISTINCT
NO, Name, IndexNumber, Type1, Type2, StockCode,
StockShortName, ISIN
FROM Entity
WHERE (Name IN
(SELECT Name
FROM Entity AS Tmp
GROUP BY Name
HAVING COUNT(*) > 1))
ORDER BY Name


I encounter this error message when I save :

"ODBC error : [Microsoft][ODBC][SQL Server Drive]]SQL Server]An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified."

I check the syntax using SQL Query Ananlyzer, it doesn't have any error and it works fine showing all the records Order By Name.
I tried to discard the syntax ORDER BY and save and it works fine too but the problem is it doesnt sort by Name.

The question :
1. Is it I can't use the syntax ORDER BY in View or something is wrong with my statement?
2. I dont understand the error message. Is it asking for extra clauses?
3. How do I need to change the statement in View in order to be able to sort by Name?

Hope someone out there can help me asap.

Thanks & Regards.

 

[fluteplr]

Take out the order by from the view.

Then when you select records from the view.

select * from myview order by whatever

A view is a virtual table there is no need for it to be ordered, just a table is not ordered, you order the data when you select from it.

 

[tlbroadbent]

As Flluteplr said, you don't need to order a view. You can Select from a view and order the result set returned by the query. Ordering a view will add unnecessary processing.

However, if there is a compelling need, the error message tells how it can be done but doesn't provide much detail explanation. The message says, "unless TOP is also specified." What does that mean and how can you implement this? Here is an example.

Create View vMyView As

SELECT TOP 100 Percent
NO, Name, IndexNumber,
Type1, Type2, StockCode,
StockShortName, ISIN
FROM Entity
WHERE (Name IN
(SELECT Name
FROM Entity AS Tmp
GROUP BY Name
HAVING COUNT(*) > 1))
ORDER BY Name

Creating the view with the TOP 100 PERCENT clause allows you to also include the ORDER BY clause.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[fluteplr]

I agree that the top 100 percent bit will work, however have to agree with Joe Celko on this one. There is no reason to ever sort a view in this manner. (He also slammed MS for allowing it, if I remember correctly.)

If someone can present a 'compelling' reason for doing this I would be interested to hear what it was.