**Use CASE to ORDER results.. How?**

12/10/2018 11:47

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_bFROM exampletableORDER BY (helpme_a * helpme_b) As you can see, I would like to use results from the CASEs in my ORDERBY.

Can this be done? A second question, maybe relevant as well, is this. On my homecomputer I can use calcualtions in the ORDER BY (like ORDER BY (blabla* blibli) ) but when I upload this to my server this doesn't workanymore. They both use MySQL 5.0. Where could be the problem? Hope anyone can help, Kind regards, Pim Zeekoers

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_bFROM exampletableORDER BY (helpme_a * helpme_b) As you can see, I would like to use results from the CASEs in my ORDERBY.

Can this be done? A second question, maybe relevant as well, is this. On my homecomputer I can use calcualtions in the ORDER BY (like ORDER BY (blabla* blibli) ) but when I upload this to my server this doesn't workanymore. They both use MySQL 5.0. Where could be the problem? Hope anyone can help, Kind regards, Pim Zeekoers

*Source is Usenet: comp.databases.mysql*

**Sign in to add a comment**

**Answer score: 5**

12/10/2018 11:47 - pim@impulzief.nl schreef: u cannot use (the results of ) an alias in the query see: http://tinyurl.com/5ngscu

*Source is Usenet: comp.databases.mysql*

**Sign in to add a comment**

**Answer score: 5**

12/10/2018 11:47 - 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 tocalculate 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_bFROM exampletable Of course, this example can be written in a lot better way but I wantto know if I can use the results of a CASE to calculate with in afollowing CASE Thanks for your quick help btw.

Pim

However, I am still not able to use the result of the first CASE tocalculate 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_bFROM exampletable Of course, this example can be written in a lot better way but I wantto know if I can use the results of a CASE to calculate with in afollowing CASE Thanks for your quick help btw.

Pim

*Source is Usenet: comp.databases.mysql*

**Sign in to add a comment**

**Answer score: 5**

12/10/2018 11:47 - pim@impulzief.nl 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

*Source is Usenet: comp.databases.mysql*

**Sign in to add a comment**

**Answer score: 5**

12/10/2018 11:47 - pim@impulzief.nl 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)

*Source is Usenet: comp.databases.mysql*

**Sign in to add a comment**

**Answer score: 5**

*Source is Usenet: comp.databases.mysql*

**Sign in to add a comment**

**Answer score: 5**

12/10/2018 11:47 - 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_bFROM exampletable Is this the same bug or should I write this differently? Pim

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_bFROM exampletable Is this the same bug or should I write this differently? Pim

*Source is Usenet: comp.databases.mysql*

**Sign in to add a comment**

**Answer score: 5**

12/10/2018 11:47 - 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 justmadeFROM creditsORDER BY (credits.column2 * justmade) Is this allowed and possible? As said, this seems to work but notalways.

Pim

But it is possible to use them in the ORDER BY right? Example: SELECT CASE WHEN credits.column1 > 0 THEN 100 END AS justmadeFROM creditsORDER BY (credits.column2 * justmade) Is this allowed and possible? As said, this seems to work but notalways.

Pim

*Source is Usenet: comp.databases.mysql*

**Sign in to add a comment**

Hide commentsHide comments