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)
>.
>