Use CASE to ORDER results.. How?

Use CASE to ORDER results.. How?

Post by pim » Sun, 13 Jul 2008 21:42:28


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
 
 
 

Use CASE to ORDER results.. How?

Post by Luuk » Sun, 13 Jul 2008 21:53:27


XXXX@XXXXX.COM schreef:

http://www.yqcomputer.com/



--
Luuk

 
 
 

Use CASE to ORDER results.. How?

Post by pim » Sun, 13 Jul 2008 22:03:48


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
 
 
 

Use CASE to ORDER results.. How?

Post by Luuk » Sun, 13 Jul 2008 22:09:48


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
 
 
 

Use CASE to ORDER results.. How?

Post by pim » Sun, 13 Jul 2008 22:17:58

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
 
 
 

Use CASE to ORDER results.. How?

Post by Luuk » Sun, 13 Jul 2008 22:33:45

XXXX@XXXXX.COM schreef:

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

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


--
Luuk
 
 
 

Use CASE to ORDER results.. How?

Post by pim » Sun, 13 Jul 2008 22:48:09

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
 
 
 

Use CASE to ORDER results.. How?

Post by Luuk » Sun, 13 Jul 2008 23:13:46

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