LAs Posted September 16, 2013 Share Posted September 16, 2013 Hello guys, i need some experienced info here.. i need to sort selected data, thats why i used case like: SELECT * FROM Customers ORDER BY CASEWHEN CustomerName LIKE '%a%' THEN 1WHEN CustomerName LIKE '%b%' THEN 2ELSE 3END Everythings fine here, but I need to sort each sector (1, 2 and 3) seperately by CustomerName`s lenght. How to do that? any ideas ? Link to comment Share on other sites More sharing options...
justsomeguy Posted September 16, 2013 Share Posted September 16, 2013 You can order further by LENGTH(CustomerName). Link to comment Share on other sites More sharing options...
LAs Posted September 17, 2013 Author Share Posted September 17, 2013 (edited) You can order further by LENGTH(CustomerName). And where should i put this function ? Because i was trying to put it everywhere, still doesnt work.. Should i put it smth like: SELECT * FROM Customers ORDER BY CASEWHEN CustomerName LIKE '%a%' THEN 1 ORDER BY LENGHT(CustomerName)WHEN CustomerName LIKE '%b%' THEN 2 ORDER BY LENGHT(CustomerName)ELSE 3 ORDER BY LENGHT(CustomerName)END I mean, if i have data like: "house" "cup" "apartament" "yard" "bell" "ben" and in my order (without sorting by lenght) it appears like: apartament, yard, bell, ben, house, cup. if i put selected data in sectors, by which case it was selected, it would look like this: (1 order): apartament, yard, ball (2 order): bell, ben (3 order): house, cup and i need to sort them by length in DESC order, so it should look like this: (1 order): ball, yard, apartament. (2 order): ben, bell. (3 order): cup, house. so all selected data should appear like: ball, yard, apartament, ben, bell, cup, house. how to do that with case and order by length? Edited September 17, 2013 by LAs Link to comment Share on other sites More sharing options...
justsomeguy Posted September 17, 2013 Share Posted September 17, 2013 Because i was trying to put it everywhere, still doesnt work.. Well, then you obviously didn't try it everywhere. How about this: SELECT * FROM Customers ORDER BY CASEWHEN CustomerName LIKE '%a%' THEN 1 WHEN CustomerName LIKE '%b%' THEN 2 ELSE 3END, LENGTH(CustomerName) It's the same as "ORDER BY a, b". In your case, "a" is the entire case..end statement and "b" is the length. Link to comment Share on other sites More sharing options...
LAs Posted September 18, 2013 Author Share Posted September 18, 2013 Holly crap.. seriously, thouht I was trying to do this trick, seems like no. We can close this thread. Link to comment Share on other sites More sharing options...
LAs Posted October 16, 2013 Author Share Posted October 16, 2013 Ok guys, need to wake up this thread again. When i`m using sorting method with "order by case" and when ... then [number], i can use only 255 arguemnts in this state. The words like "when" "then" are described as variables (am i write?). Ok, so how to avoid those arguments limit ? What kind of trick do you know and using, guys ? Link to comment Share on other sites More sharing options...
justsomeguy Posted October 16, 2013 Share Posted October 16, 2013 In general, those words are called keywords. That also includes things like SELECt, FROM, WHERE, etc. There are different types of keywords though. For CASE, I believe that is a statement (the entire CASE..END block would be referred to as a case statement). Another generic term is expression, an expression is anything that will evaluate to a value. So the case statement is also an expression since it will evaluate to a number. This is the manual for the case statement: http://dev.mysql.com/doc/refman/5.0/en/case.html Look at the note on the top of that page. It says that there is both a case statement and case expression. It sounds like the case statement that they show is for stored procedures, and the case expression is for regular queries. It looks like the major difference is that the case statement ends with END CASE and the expression ends with END. This is the manual page for the case expression: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html They also refer to it as the case operator, but that's a pretty loose definition of an operator. Other operators are things like +, -, AND, OR, etc. Inside the case, the WHEN..THEN would probably be referred to as a when clause or when expression. There's some discussion about that issue here: http://stackoverflow.com/questions/1160459/sql-limit-on-case-number-of-when-then-conditions It looks like there a limit of 255 expressions. If you need more than that then you can nest the case statement (i.e. make the else another case statement that lists more options), or that page suggests storing the values in a temporary table instead of using a case. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now