I have completed an SQL statement that displays data using SELECT. I would
like to put the SQL in a View so my user could open it easily. But, I need
to sort the first two columns alphabetically.

To use Order By in a View, I understand that I need to include a TOP clause.
Can I use TOP 100% to return all data in the table and then use my current
Order By statement to sort the data? If yes, where do I place the TOP clause.

Thanks.

Hi James,
You don't normally place order by clauses in views (you can but normally you
don't)

the syntax is

Select TOP 10 ol1, col2 from tablename order by col1,col2


--
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com

[quoted text, click to view]

[quoted text, click to view]

Views are always unsorted. Using TOP 100% to "force" the view to be ordered
is not a reliable method. Even if it appears to work for you it will most
likely fail at some time in the future.

The correct way is to use ORDER BY when you query the view, not in the view
definition itself:

SELECT ...
FROM your_view
ORDER BY col1, ...

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

Thanks for the help David. Let's see if I understand it.

I cannot sort my view in the code that creates a view. I cannot, for
example, write code creating a view and then immediately follow it with code
like this:

SELECT * FROM [myview]
Order By ..........

Instead, I need two steps. First I create the view. Then I create the
above code and run it in SQL Query Analyzer or save it in a stored procedure.
Since my users don't know SQL, I am planning to use the stored procedure.

The user can then run the stored procedure in two ways:

1. Run it from SQL Query Analyzer by entering EXEC [stored procedure name]
2. Run it from their browser by entering the appropriate http:// syntax

I don't see a way to run the stored procedure from Enterprise Manager.

My only other option is to skip the view. Put the code that creates both
the view and the sort in the stored procedure.

Am I missing anything? Are there other options for getting a sorted query
to my user?

Thanks.

[quoted text, click to view]

I agree that a proc is the best way to go. For most applications it
makes sense to perform all user data access through stored procs. Use
ORDER BY in your procs to sort data for the user.

You can't create a view in a proc though (unless you use dynamic SQL,
in which case it wouldn't strictly happen in the proc at all). Nor
should you need to. Views are part of the database schema and as such
should normally remain static at runtime.

--
David Portas
SQL Server MVP
--

David,

I agree with you that the best method is to ORDER on the return to the
client, but your statement intrigued me:

"Views are always unsorted. Using TOP 100% to "force" the view to be
ordered
is not a reliable method. Even if it appears to work for you it will
most
likely fail at some time in the future. "

Why would it fail?

Stu

I don't think that it will fail.
Here's some text taken from this site:
http://www.informit.com/articles/article.asp?p=130855&rl=1

The TOP syntax was introduced in SQL Server 7.0 to provide the equivalent of
an Access "top values" query, although there are some subtle differences.
The TOP syntax with its associated ORDER BY clause are Transact-SQL
extensions and are not part of the ANSI SQL-92 standard. As far as the ANSI
SQL-92 standard is concerned, the definition of a view is unordered.

So.. "insert here Celko's standard rant on using portable code"

[quoted text, click to view]

[quoted text, click to view]

One last comment on Ordered Views.
Example of a View with Order By called "contactInfo":
SELECT TOP 100 percent lastName, firstName, ... zipCode, city FROM... ORDER
BY lastName, firstName

In my WEB page I want this:
Select ... from contactInfo WHERE zipCode = '123456'

What good did it do to have ordered the View?

I guess my question is why? I mean, why would TOP 100 PERCENT...ORDER
BY work outside of a view, but faile within a view? And why would it
apparantly do so without warning as indicated by both yours and David's
response?

Again, I'm not saying that this is a method that I agree with; I'm just
curious as to why it would work only randomly.

Stu

[quoted text, click to view]

Hi Raymond,

It do think it will fail.

The ORDER BY in a VIEW is only allowed in conjuncion with a TOP. That
means that the ORDER BY is used to determine which rows to return. There
still is no guarantee that
SELECT something FROM theview
will return the rows in the same order as the view's ORDER BY clause.

Of course, TOP 100 PERCENT is in itself an oddity.

It's true that I've never seen a script to prove this for SQL Server
2000. But for SQL Server 2005, I've already read complaints that their
views suddenly were not ordered anymore. Unfortunately (for them), this
order was never guaranteed, so it's not considered an uncompatibility by
Microsoft.

Best, Hugo
--

The problem is not so much that TOP 100 PERCENT ORDER BY "doesn't work"
in views. It does exactly what it's supposed to - it returns a subset
of the data in the table (i.e. 100% of the data). However, if you then
QUERY the view using a SELECT statement WITHOUT an ORDER BY clause then
the order returned by that SELECT statement is undefined - as it is for
any other query without an ORDER BY clause. The problem arises because
some people make the unwise and unsupported assumption that the ORDER
BY in the view will always be honoured by the query.

Clearly there are cases where it would be better for the optimizer to
ignore the ORDER BY in the views (you just gave us an example: Select
.... from contactInfo WHERE zipCode = '123456') and this is exactly what
happens frequently in SQL Server 2005. Since the resulting order is
undefined there's no reason why it couldn't change in SQL Server 2000
also - maybe under some SP or hotfix for example.

MS has made this much clearer in 2005 BOL:

http://msdn2.microsoft.com/en-us/library/ms188723.aspx

<quote>

ORDER BY guarantees a sorted result only for the outermost SELECT
statement of a query. For example, consider the following view
definition:

CREATE VIEW TopView AS
SELECT TOP 50 PERCENT * FROM Person.Contact
ORDER BY LastName

Then query the view:

SELECT * FROM TopView

Although the view definition contains an ORDER BY clause, that ORDER BY
clause is used only to determine the rows returned by the TOP clause.
When querying the view itself, SQL Server does not guarantee the
results will be ordered, unless you specify so explicitly, as shown in
the following query:

SELECT * FROM TopView
ORDER BY LastName

</quote>

--
David Portas
SQL Server MVP
--

Don't get me wrong, I don't use Order By in views.
But this is just stupid.
Microsoft adds the extension to the ANSI standard, documents it, and the
order still isn't guaranteed.
Whoever decided this should stand in the corner for a week... without pay.

Hugo, can you point me to where Microsoft says that the order is not
guaranteed.
I can't seem to find this in BOL.

Microsoft adds
[quoted text, click to view]

[quoted text, click to view]

Hi Stu,

IMO, Microsoft made a mistake in the design of the TOP operator. Instead
of choosing a new keyword to define the ordering to be used in finding
the TOP xx rows (e.g. "SELECT (TOP 75 BY Age ASC) Col1, Col2"), they
chose to overload the existing ORDER BY keyword.

This resulted in some sillyness (like not allowing ORDER BY in a view or
subquery, unless accompanied by a TOP). This was made worse by the fact
that the results were apparently always returned in an order that
matches the ORDER BY - probably due to the way the optimizer handles the
TOP .. ORDER BY in the execution plan. This led people to believe that
this was a reliable coding trick, even though this behaviour was not
documented.

In one of the older versions of SQL Server, a GROUP BY would allways
result in a sort. Though this was undocumented, developers still relied
on this and omitted the explicit ORDER BY from their queries. When MS
added new execution plan strategies that would perform a GROUP BY
without sorting, this code broke.

L'histoire se répète!

Best, Hugo
--

[quoted text, click to view]

Hi Raymond,

It took me quite a while, but I did find one place where this is
explicitly mentioned. Not a place where you'd expect it, though.

Open BOL, go to the index, then navigate to ORDER BY clause /
compatibility issues. Here, you'll find this text:

"An explicit ORDER BY clause for a SELECT statement is required to
ensure any useful ordering of data. (...) Add an explicit ORDER BY
clause to all SELECT statements needing to produce ordered rows."

Of course, that's not much documentation and it's not in a place where
one is likely to look. I don't think that there are any explicit
disclaimers about the effects of TOP ... ORDER BY in a view or subquery.
But there are also no statements that this WILL have the effect of
creating an "Ordered View", as it's been called in this thread. In fact,
the term "Ordered View" doesn't appear in BOL at all. That's why I (and
many others) have always warned that this usage of TOP 100 PERCENT ...
ORDER BY in a view is *undocumented* and that it *might* break.

Best, Hugo
--

So, what I've garnered from both you and David's replies is that it's
NOT a programming bug, but rather a potential for abuse by current
developers and/or a target for drastic change in future editions. Not
trying to be pedantic, but that's a little different than saying that
the code will fail, IMO.

:)

Stu

[quoted text, click to view]

Hi Stu,

Even more pedantic: it's neither a bug, nor a potential for abuse, but a
coincidental side effect of the implementation in SQL Server 2000.

Two months ago, I would have agreed with the words "it might fail". But
not anymore, since I have already read reports that any code that relies
on this side effect WILL fail after upgrading to SQL Server 2005.

If you're on SQL Server 2000 and you'll never install a service pack,
then it MIGHT fail (I've never seen reports of this, but here's no
guarantee that it won't).

If you're on SQL Server 2000 and you will install future service packs,
then it WILL PROBABLY fail.

If you're planning to upgrade to SQL Server 2005, then it WILL almost
CERTAINLY fail.

Best, Hugo
--