ORDER BY in VIEW not working

[Beowulf]

I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don't come through
sorted by Numb.

Everything I've read on the web suggests that including the TOP
directive should enable ORDERY BY in views. Does someone have an idea
why the sorting is not working correctly for this particular view? thanks.

CREATE VIEW vwRouteReference
AS
SELECT TOP 100 PERCENT tblItem.ID,
tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,
tblItem.Numb, tblQuestion.DescrPrimary AS Type
FROM tblItem INNER JOIN tblQuestion
ON (tblItem.ID = tblQuestion.Item_ID)
WHERE (((tblItem.Category_ID)>0))
UNION
SELECT TOP 100 PERCENT tblItem.ID,
tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS FullName,
tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS Type
FROM tblItem
WHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR
(tblItem.Type) = 'Route'))
ORDER BY tblItem.Numb

[Herb Caudill]

The problem is that the criteria for what subset to be return might be
simple or complicated - but at any rate it's not just a matter of
passing one or two parameters. For instance, in the past I might have
had:

view v_ArticlesBase -- in SS2000, provided ordered results
procedure GetAllArticlesByProgram(@ProgramID)
procedure GetRecentArticlesByProgram(@ProgramID)
procedure GetArticlesByArticle(@ArticleID) -- related articles
procedure GetArticlesByProject(@ProjectID)

... and so on. At the very least, the ordering logic needs to be
repeated in each of these stored procedures.

An alternative would be to create a single monstrous procedure that
consolidates the ordering logic but that uses a lot of conditional paths
to provide all of the subsets listed above - but that seems really
inelegant. Some of these are doing simple matches, while others are
having to traverse one or two linked tables to determine if an article
should be included in the subset.

*** Sent via Developersdex http://www.developersdex.com ***

[David Portas]

Herb Caudill wrote:

The problem is that the criteria for what subset to be return might be
simple or complicated - but at any rate it's not just a matter of
passing one or two parameters. For instance, in the past I might have
had:

view v_ArticlesBase -- in SS2000, provided ordered results
procedure GetAllArticlesByProgram(@ProgramID)
procedure GetRecentArticlesByProgram(@ProgramID)
procedure GetArticlesByArticle(@ArticleID) -- related articles
procedure GetArticlesByProject(@ProjectID)

.. and so on. At the very least, the ordering logic needs to be
repeated in each of these stored procedures.

An alternative would be to create a single monstrous procedure that
consolidates the ordering logic but that uses a lot of conditional paths
to provide all of the subsets listed above - but that seems really
inelegant. Some of these are doing simple matches, while others are
having to traverse one or two linked tables to determine if an article
should be included in the subset.

Well if your logic is as varied as you are making it sound then it's
possibly unlikely that the equivalent SELECTs out of a view would
always respect the TOP 100 PERCENT ORDER BY in a view - even in 2000.
Not unless you use SELECT * in every query perhaps, and we know that
SELECT * is bad practice. Contrary to what you believed, the results in
2000 won't always follow the ORDER BY included in a view.

Also, if you are putting all this logic client-side today then it seems
like you must be constructing a lot of dynamic SQL strings. You said
you are a website developer so I hope you are aware of the SQL
injection issue and what precautions you have to take to prevent
injection from user-supplied parameters. In most cases the simplest and
most reliable solution is to use parameterized procs.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Erland Sommarskog]

Herb Caudill (he**@caudillweb.com) writes:

OK, OK, OK. Got it. Point is it used to work that way and all of us
stupid schlubs who failed to read the fine print took advantage of the
way it worked in practice.
An analogy: you build houses, and you use bricks and stone from some
place, and you think they are really great. Then one day someone tells
you that there is a lot of radon in the material...
Thanks - I'll look into this. At first glance, it seems like a very
complicated solution to (what used to be) a very simple problem.

But it has the distinct advantage of being a solution on which you
actually can rely.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>> My goodness, Celko. Where is this anger coming from? <,

Hey I thought the "Satan line" would register with anyone who has hs
to maintain code

Why do you think this is the right tool for websites? Not portable,
never meant to do this, etc.

[--CELKO--]

Why are you talking about .NET, which has nothing to do with RDBMS?
You cna JOIN agianst VIEWS, not Stored Procedures. You still think in
terms of Objects (which do not exist in set-oriented SQL) and
sequential processes via cursors.