Order for conditions to be processed

what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)Originally posted by kiranghag
what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)

If all the conditions are AND'ed then the order will be left to right, if the first condition is false the entire thing will be false so the remaining conditions would not be processed.
And if the conditions are OR'ed then if the first condition is true then all the conditions are true.
Also adding parenthesis decides which conditions are processesed when.

Regards,
Harshal.|||Originally posted by kiranghag
what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)

sql has an internal parser which reorders the conditions depending on clustered/indexes and such (keys for example). Conditions that refer to the c/indexes are processed prior to the non-c/indexes.

Besides that, the order in which the other conditions are processed might get reversed, which ever suit sql best.|||Kaiowas, I think you're talking about the order in which the records are retrieved in the case of SELECT, or UPDATEed/DELETEed respectively.

harshal's statement pretty much summarizes what the optimizer does and how to control it.

Order By - Conditional

I wish to include a condition in my order by statement.
i.e. If the value of Field X > Y then Order by Field X,
else Order by Field Z.
I have tried inserting a select case but i cannot get it
to work
Here is an extract from the table
Order_ID Status Name
50 10 Michael
51 10 John
52 10 Peter
53 20 Adam
54 20 Bruce
The condition I would like to apply is as follows:
If Status is greater than 11, Order by Name, else order by
Order_ID
Any help wpuld be greatly appreciated
On Fri, 13 Aug 2004 05:04:53 -0700, Wes wrote:

>I wish to include a condition in my order by statement.
>i.e. If the value of Field X > Y then Order by Field X,
>else Order by Field Z.
>I have tried inserting a select case but i cannot get it
>to work
>Here is an extract from the table
>Order_ID Status Name
>50 10 Michael
>51 10 John
>52 10 Peter
>53 20 Adam
>54 20 Bruce
>The condition I would like to apply is as follows:
>If Status is greater than 11, Order by Name, else order by
>Order_ID
>Any help wpuld be greatly appreciated
Hi Wes,
Something like this?
ORDER BY Status,
CASE WHEN Status > 11 THEN Name END,
CASE WHEN Status <= 11 THEN Order_ID END
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I have tried the following but with no success...
ORDER BY CASE WHEN Status > 11 THEN Name END,
CASE WHEN Status <= 11 THEN Order_ID END
Is there any other approach I can use?
Thanks in advance
[vbcol=seagreen]
>--Original Message--
>On Fri, 13 Aug 2004 05:04:53 -0700, Wes wrote:
by
>Hi Wes,
>Something like this?
>ORDER BY Status,
> CASE WHEN Status > 11 THEN Name END,
> CASE WHEN Status <= 11 THEN Order_ID END
>
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>
|||On Wed, 15 Sep 2004 10:14:33 -0700, Wes wrote:

>I have tried the following but with no success...
>ORDER BY CASE WHEN Status > 11 THEN Name END,
>CASE WHEN Status <= 11 THEN Order_ID END
>
>Is there any other approach I can use?
>Thanks in advance
Hi Wes,
Could you be more specific? What happened when you tried the code above?
Did you get an error? Unexpected results? Explosions in the server room?
I don't see anything wrong with the code snippet above. To goo further
into this, I must reproduce your situation on my system. To do that, you
should post more information. To be precise, I need:
1. Table structure, posted as DDL (CREATE TABLE statements; irrelevant
columns may be omitted but please do include all constraints);
2. Enough sample data to show what you want to achieve, posted as INSERT
statements;
3. The output you expect from the sample data you posted;
4. The complete SQL statement you are currently using plus the output you
are getting from it - if it's an error message, please copy and paste the
complete text;
5. A short and concise description of the business problem you're trying
to solve.
With that, I should be able to reproduce your problem and search for a
solution.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||My apologies Hugo,
the query was correct.
When building test data to send you, I noticed that the
only problemn i had was the order of the 2 case
statements. i.e. I switched the case statements
instead of ...[vbcol=seagreen]
i used ...[vbcol=seagreen]
Thanks for you help!
Wes

>--Original Message--
>On Wed, 15 Sep 2004 10:14:33 -0700, Wes wrote:
>
>Hi Wes,
>Could you be more specific? What happened when you tried
the code above?
>Did you get an error? Unexpected results? Explosions in
the server room?
>I don't see anything wrong with the code snippet above.
To goo further
>into this, I must reproduce your situation on my system.
To do that, you
>should post more information. To be precise, I need:
>1. Table structure, posted as DDL (CREATE TABLE
statements; irrelevant
>columns may be omitted but please do include all
constraints);
>2. Enough sample data to show what you want to achieve,
posted as INSERT
>statements;
>3. The output you expect from the sample data you posted;
>4. The complete SQL statement you are currently using
plus the output you
>are getting from it - if it's an error message, please
copy and paste the
>complete text;
>5. A short and concise description of the business
problem you're trying
>to solve.
>With that, I should be able to reproduce your problem and
search for a
>solution.
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>