hi Sylvain - thanks for your response. It gives me some good insights.
--
cinnie


"Sylvain Lafontaine" wrote:

HI Cinnie - For your original question, the answer are both yes and no:
depending on what you are asking in your query and how it wil be build by
the sql compiler - whatever its real name - expression in a select statement
can be computed before or after any sorting order and it's an error to build
a particular query on the assumption that there is a definite order between
them.

For example, Itzik Ben-Gan has recently given an example where the addition
of a covering index change the order of which an expression is calculated
either after or before the Order By:

http://www.sqlmag.com:80/Article/Art...33/103533.html

This is for SQL-Server 2005/8 but it shows you how there is not such a thing
as an evaluation order between the Select part and an Order By instruction.

For the rest of my comment, you are right when you say that it goes against
standard convention found in most VBA and SQL texts. However, you are not
obligated to follow them and personally, this is what my experience told me
what it's best to do. While its usually a good thing to follow standards,
not because they are good but because they save a lot of work for peoples
working in the same field, this is not always the case and many of them are
now thought of as beeing counterproductive and are no longer followed by
many - if not most - peoples working on real working cases.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"cinnie" wrote in message
...
hi Sylvain - thank you for your insights. I still don't feel that my
original question has found an answer, although I do think I get now what
you
are saying about ALIAS. I'm simply trying to get a better understanding
of
how SQL is processed.

Further to the examples I mentioned in my original post, I think that
there
is further evidence that ORDER BY must be processed before SELECT: How
could
the clause SELECT TOP 3... be processed if the records weren't already
sorted
in the ORDER BY clause?

Unrelated to my original question, I found your comments about using
prefixes like 'tbl' curious. You say that names like this are 'ugly', but
I
was just striving for clarity, not beauty. I'm fairly new at this and
don't
wish to question an MVP, but doesn't this run counter to standard
conventions
found in most VBA and SQL texts?

thanks for your thoughts
--
cinnie


"Sylvain Lafontaine" wrote:

The problem here is not the order of execution of the Select and of the
Order By but it is the name's aliasing; ie., the creation/definition of
the
names that will be given to each column returned into the resultset and
which is performed last. You cannot use an alias in an Order By because
they have not yet been produced at this stage; even if the individual
column/expression have already been computed.

If you want to use an Expression an in Order by, you must either repeat
the
Expression or use its column number:

... Order by tblEmp.Name_F & ", " & tblEmp.Name_L

... Order by 2

However, I would suggest *NOT* to use the column number because if you
ever
want to add or remove a column, it's quite likely that you will either
forget to change the Order By or miscaculate its new position. Also,
when
someone else will read your quote, it will make it much more harder to
understand what's really happening here.

In my opinion, in your case, the best solution would be to make Order By
on
both field separately:

... Order by tblEmp.Name_F, tblEmp.Name_L

It's not only a question of syntaxe but because on big database, the
database engine can use an index on the fields Name_F and Name_L is such
an
index exists; whereas whenever you use an expression, you are cutting
your
query from the possibility of using any index.

Finally, your names tblEmp.Name_F are ugly and will give you a lot of
trouble later. By experience, it's much better to use fully qualified
names
because they are autodocumenting themselves:

tblEmployees.FirstName, tblEmployees.LastName

and even better, dropping the prefixe tbl - which is totally useless -
and
using the singular form for Employee because later, when you will have to
deal with objects and collection of objects if you go to .NET, it will
make
the integration easier:

Employee.FirstName + Employee.LastName

And finally, in english, it's not NameFull but FullName. Everytime you
go
against your brain, you diminish productivity and rise the possibility of
making errors not only for yourself but for all these other peoples who
will
have to read your code later.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"cinnie" wrote in message
...
hello

In SQL, is SELECT executed before ORDER BY, or after. Here's why I'm
confused about this;

Consider table tblEmp with fields EmpID, Name_F, Name_L, Wage

SELECT tblEmpID, tblEmp.Name_F & ", " & tblEmp.Name_L AS
Name_Full
FROM tblEmp
ORDER BY tblEmp.Wage

This works fine, even though the Wage field in the ORDER BY statement
is
not
included in the SELECT statement. This makes me think that ORDER BY is
executed BEFORE the SELECT statement has been able to eliminate the
Wage
column.

Also, SELECT tblEmpID, tblEmp.Name_F & ", " & tblEmp.Name_L AS
Name_Full
FROM tblEmp
ORDER BY Name_Full

This doesn't work because the ORDER BY statement doesn't recognize
Name_Full. This again makes me think that the ORDER BY is executed
BEFORE
the SELECT.

But.....

when I research SQL order of operations, most sites seem to claim that
the
ORDER BY clause is executed AFTER the SELECT clause. What gives? Any
clarification?

thank you in advance
--
cinnie


.



.