ORDER BY calculated field

Hello all,
I have a query i’d like to sort based on a field derived from other fields
from that same query.
I’d like to know if my field would then be calculated twice (Once in the
SELECT clause, another one in the ORDER BY clause). In this case it would
probably be better to use a subquery.
More concretly here are my 2 options :
Option 1 :
SELECT
data2.entity_nr,
data2.column_b,
data2.column_c,
data2.column_d,
data2.column_e,
data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
FROM
data2
ORDER BY
data2.column_b-data2.column_c-data2.column_d+data2.column_d DESC --column f
Option 2 :
SELECT * FROM (
SELECT
data2.entity_nr,
data2.column_b,
data2.column_c,
data2.column_d,
data2.column_e,
data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
FROM
data2
)derived
ORDER by derived.column_f DESC
Which one would you suggest ? (maybe a third one ;))
Tx
NicolasHi Nico
using derived tables will probably have less cost . But why dont you use
execution plan and see for yourself.
Regards
R.D
"Nico" wrote:

> Hello all,
> I have a query i’d like to sort based on a field derived from other fiel
ds
> from that same query.
> I’d like to know if my field would then be calculated twice (Once in the
> SELECT clause, another one in the ORDER BY clause). In this case it would
> probably be better to use a subquery.
> More concretly here are my 2 options :
> Option 1 :
> SELECT
> data2.entity_nr,
> data2.column_b,
> data2.column_c,
> data2.column_d,
> data2.column_e,
> data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
> FROM
> data2
> ORDER BY
> data2.column_b-data2.column_c-data2.column_d+data2.column_d DESC --colu
mn f
> Option 2 :
> SELECT * FROM (
> SELECT
> data2.entity_nr,
> data2.column_b,
> data2.column_c,
> data2.column_d,
> data2.column_e,
> data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
> FROM
> data2
> )derived
> ORDER by derived.column_f DESC
> Which one would you suggest ? (maybe a third one ;))
> Tx
> Nicolas
>|||>I'd like to know if my field would then be calculated twice
Also note that you can use an alias in the order by. So there is no need to
repeat the expression.
SELECT
data2.entity_nr,
data2.column_b,
data2.column_c,
data2.column_d,
data2.column_e,
data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
FROM
data2
ORDER BY column_f DESC
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:248BE97E-1C68-48FA-B5DE-B02B089B700D@.microsoft.com...
> Hello all,
> I have a query i'd like to sort based on a field derived from other fields
> from that same query.
> I'd like to know if my field would then be calculated twice (Once in the
> SELECT clause, another one in the ORDER BY clause). In this case it would
> probably be better to use a subquery.
> More concretly here are my 2 options :
> Option 1 :
> SELECT
> data2.entity_nr,
> data2.column_b,
> data2.column_c,
> data2.column_d,
> data2.column_e,
> data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
> FROM
> data2
> ORDER BY
> data2.column_b-data2.column_c-data2.column_d+data2.column_d
> DESC --column f
> Option 2 :
> SELECT * FROM (
> SELECT
> data2.entity_nr,
> data2.column_b,
> data2.column_c,
> data2.column_d,
> data2.column_e,
> data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
> FROM
> data2
> )derived
> ORDER by derived.column_f DESC
> Which one would you suggest ? (maybe a third one ;))
> Tx
> Nicolas
>