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

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

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
12/10/2018 11:47 - pim@impulzief.nl schreef: http://bugs.mysql.com/bug.php?id=11694

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

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

Source is Usenet: comp.databases.mysql
Sign in to add a comment

eDiscover
Helpforce eDiscover provides technical articles updated each dayHelpforce eDiscover RSS feed contains the latest technical articles in RSS
Click the logo to go back to the main page
Search eDiscover
  
Categories

Click an icon to go to that category

Helpforce eDiscover contains articles about Microsoft Windows Helpforce eDiscover contains articles about Apple products and MacOS Helpforce eDiscover contains articles about Linux and POSIX operating systems Helpforce eDiscover contains articles about Helpforce Helpforce has a large variety of technical information and articles for you to read Helpforce eDiscover contains articles about databases, MYSQL, SQL Server Oracle Helpforce eDiscover contains articles about Java, JVM and the JRE Helpforce eDiscover contains articles about the QNX operating system Helpforce eDiscover contains articles about Oracle Solaris and Open Solaris Helpforce eDiscover contains articles about RISC OS, Acorn and the BBC Micro Helpforce eDiscover contains articles about Amiga and AmigaOS

Type your comment into the box below