Function in an ORDER BY statement

[Jack Leach]

Hi,

I have a query that calculates a person's common name through a BuildName
function. This is also the ORDER BY in the sql... I notice that the function
itself is called twice for each record, once for the common name calculation
and another for the order...


"SELECT tblEmployees.fldID, " _
BuildName([fldLastName], _
[fldFirstName],1 _
) AS fldName _
FROM tblEmployees _
ORDER BY BuildName([fldLastName], _
[fldFirstName],1)



Is there no way to order the query by the calculated field without having to
call the function twice? Just a performance concern... I hate to see things
done twice.

Thanks!

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jeanette Cunningham]

Hi Jack,
you could include fldLastName in the query and order by LastName. Mark this
column as not showing.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

Hi,

I have a query that calculates a person's common name through a BuildName
function. This is also the ORDER BY in the sql... I notice that the function
itself is called twice for each record, once for the common name calculation
and another for the order...


"SELECT tblEmployees.fldID, " _
BuildName([fldLastName], _
[fldFirstName],1 _
) AS fldName _
FROM tblEmployees _
ORDER BY BuildName([fldLastName], _
[fldFirstName],1)



Is there no way to order the query by the calculated field without having to
call the function twice? Just a performance concern... I hate to see things
done twice.

I'm not sure what BuildName returns; is it something other than either "Leach,
Jack" or "Jack Leach"?

In any case, you could leave the Order By independent of the function; just

ORDER BY fldLastName, fldFirstName

or vice versa, unless BuildName is adding something else that you need in the
sort.

 

[Marshall Barton]

I have a query that calculates a person's common name through a BuildName
function. This is also the ORDER BY in the sql... I notice that the function
itself is called twice for each record, once for the common name calculation
and another for the order...

"SELECT tblEmployees.fldID, " _
BuildName([fldLastName], _
[fldFirstName],1 _
) AS fldName _
FROM tblEmployees _
ORDER BY BuildName([fldLastName], _
[fldFirstName],1)

Is there no way to order the query by the calculated field without having to
call the function twice? Just a performance concern... I hate to see things
done twice.

You should be able to sort using the number of the field in
the select list:
ORDER BY 2

 

[Daniel Pineault]

Jack,

Just a thought, I am by no means an expert on the subject, but what about a
sub-query?

SELECT TmpTbl.fldID, TmpTbl.fldName
FROM(
SELECT tblEmployees.fldID, BuildName([fldLastName], [fldFirstName], 1) AS
fldName
FROM tblEmployees
) AS TmpTbl
ORDER BY TmpTbl.fldName;

I do not know if this would give better performance or not. Hopefully, one
of the MVPS or Experts may weigh in on the matter and shed some light.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Jack Leach]

I'll give it a shot... thanks

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jack,

Just a thought, I am by no means an expert on the subject, but what about a
sub-query?

SELECT TmpTbl.fldID, TmpTbl.fldName
FROM(
SELECT tblEmployees.fldID, BuildName([fldLastName], [fldFirstName], 1) AS
fldName
FROM tblEmployees
) AS TmpTbl
ORDER BY TmpTbl.fldName;

I do not know if this would give better performance or not. Hopefully, one
of the MVPS or Experts may weigh in on the matter and shed some light.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

Hi,

I have a query that calculates a person's common name through a BuildName
function. This is also the ORDER BY in the sql... I notice that the function
itself is called twice for each record, once for the common name calculation
and another for the order...


"SELECT tblEmployees.fldID, " _
BuildName([fldLastName], _
[fldFirstName],1 _
) AS fldName _
FROM tblEmployees _
ORDER BY BuildName([fldLastName], _
[fldFirstName],1)



Is there no way to order the query by the calculated field without having to
call the function twice? Just a performance concern... I hate to see things
done twice.

Thanks!

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)