A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SELECT vs. ORDER BY - order of execution question



 
 
Thread Tools Display Modes
  #1  
Old February 4th, 2010, 05:52 PM posted to microsoft.public.access.queries
cinnie
external usenet poster
 
Posts: 67
Default SELECT vs. ORDER BY - order of execution question

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
  #2  
Old February 4th, 2010, 06:37 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default SELECT vs. ORDER BY - order of execution question

It seems to process from end to start but not all cases.
This works --
SELECT [Something]+1 AS New, YourTable.Score, YourTable.Grade,
[Score]+[Grade] AS Something
FROM YourTable;

And this --
SELECT YourTable.Score, YourTable.Grade, [Score]+[Grade] AS Something,
[Something]+1 AS New
FROM YourTable;

And this --
SELECT [Score]+[Grade] AS Something, YourTable.Score, YourTable.Grade,
[Something]+1 AS New
FROM YourTable;

--
Build a little, test a little.


"cinnie" wrote:

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

  #3  
Old February 4th, 2010, 08:11 PM posted to microsoft.public.access.queries
cinnie
external usenet poster
 
Posts: 67
Default SELECT vs. ORDER BY - order of execution question

hello Karl

I'm having trouble understanding this answer because it makes no use of the
ORDER BY clause. I'm trying to determine whether ORDER BY or SELECT is
processed first in SQL. Any thoughts?
--
cinnie


"KARL DEWEY" wrote:

It seems to process from end to start but not all cases.
This works --
SELECT [Something]+1 AS New, YourTable.Score, YourTable.Grade,
[Score]+[Grade] AS Something
FROM YourTable;

And this --
SELECT YourTable.Score, YourTable.Grade, [Score]+[Grade] AS Something,
[Something]+1 AS New
FROM YourTable;

And this --
SELECT [Score]+[Grade] AS Something, YourTable.Score, YourTable.Grade,
[Something]+1 AS New
FROM YourTable;

--
Build a little, test a little.


"cinnie" wrote:

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

  #4  
Old February 4th, 2010, 08:15 PM posted to microsoft.public.access.queries
Sylvain Lafontaine[_2_]
external usenet poster
 
Posts: 247
Default SELECT vs. ORDER BY - order of execution question

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



  #5  
Old February 5th, 2010, 12:26 AM posted to microsoft.public.access.queries
cinnie
external usenet poster
 
Posts: 67
Default SELECT vs. ORDER BY - order of execution question

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



.

  #6  
Old February 5th, 2010, 04:55 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default SELECT vs. ORDER BY - order of execution question

On Thu, 4 Feb 2010 15:26:08 -0800, cinnie
wrote:

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?


MVP's disagree with MVP's about this ALL the time... g

Sometimes escalates to sectarian warfare... "Heretic!" "Blasphemer!"

--

John W. Vinson [MVP]
  #7  
Old February 5th, 2010, 05:51 AM posted to microsoft.public.access.queries
Sylvain Lafontaine[_2_]
external usenet poster
 
Posts: 247
Default SELECT vs. ORDER BY - order of execution question

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



.



  #8  
Old February 5th, 2010, 01:06 PM posted to microsoft.public.access.queries
cinnie
external usenet poster
 
Posts: 67
Default SELECT vs. ORDER BY - order of execution question

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


.



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.