Developer Forum

Dear all,

How can I use the results in my CASE pieces to help me ORDER?
Below a simple just to understand example.

SELECT CASE
WHEN column_a > 10 THEN 1
WHEN column_a > 100 THEN 2
END AS helpme_a,
CASE
WHEN column_b > 10 THEN 50
WHEN column_b > 100 THEN 100
END AS helpme_b
FROM exampletable
ORDER BY (helpme_a * helpme_b)


As you can see, I would like to use results from the CASEs in my ORDER
BY.
Can this be done?

A second question, maybe relevant as well, is this. On my home
computer I can use calcualtions in the ORDER BY (like ORDER BY (blabla
* blibli) ) but when I upload this to my server this doesn't work
anymore. They both use MySQL 5.0. Where could be the problem?

Hope anyone can help,

Kind regards,


Pim Zeekoers

XXXX@XXXXX.COM schreef:

http://www.yqcomputer.com/



--
Luuk

Dear Luuk and rest,

So this bug should have been fixed but doesn't really work?

Anyway, I also seem not able to use CASE results in a following CASE.
Like:

SELECT CASE
WHEN column_a > 10 THEN 1
WHEN column_a > 100 THEN 2
END AS helpme_a,
CASE
WHEN column_b > helpme_a THEN 50
WHEN column_b > helpme_a THEN 100
END AS helpme_b
FROM exampletable


Is this the same bug or should I write this differently?

Pim

XXXX@XXXXX.COM schreef:


no, its a bug in your query, you should change the order of the WHEN in
your query:

SELECT CASE
WHEN column_a > 100 THEN 2
WHEN column_a > 10 THEN 1
END AS helpme_a,
CASE
<=== OEPS!!!, actually you wrote the same condition TWICE ????? !!!!! ==>
WHEN column_b > helpme_a THEN 50
WHEN column_b > helpme_a THEN 100
END AS helpme_b
FROM exampletable


--
Luuk

n 12 jul, 15:09, Luuk < XXXX@XXXXX.COM > wrote:

Luuk,

You're very right about the order of the WHEN part.
However, I am still not able to use the result of the first CASE to
calculate in the second.
Another example, maybe it's just not possible I don't know:

SELECT CASE
WHEN firstcolumn = 2 THEN 2
WHEN firstcolumn = 1 THEN 1
END AS helpme_a,
CASE
WHEN secondcolumn > helpme_a THEN (50*helpme_a)
WHEN secondcolumn > helpme_a THEN (100*helpme_a)
END AS helpme_b
FROM exampletable

Of course, this example can be written in a lot better way but I want
to know if I can use the results of a CASE to calculate with in a
following CASE

Thanks for your quick help btw.


Pim

XXXX@XXXXX.COM schreef:

u cannot use (the results of ) an alias in the query

see: http://tinyurl.com/5ngscu


--
Luuk

n 12 jul, 15:33, Luuk < XXXX@XXXXX.COM > wrote:

Luuk,

Mmm... That's a pitty.
But it is possible to use them in the ORDER BY right?

Example:
SELECT CASE
WHEN credits.column1 > 0 THEN 100
END AS justmade
FROM credits
ORDER BY (credits.column2 * justmade)

Is this allowed and possible? As said, this seems to work but not
always.


Pim

XXXX@XXXXX.COM schreef:

i think this was answered as you 1st question
(see: http://bugs.mysql.com/bug.php?id=11694 )

but you can always do:

SELECT CASE
WHEN credits.column1 > 0 THEN 100*credits.column2
END AS justmade
FROM credits
ORDER BY (justmade)


--
Luuk