Jump to content

Sort using CASE and LEN


Recommended Posts

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 post
Share on other sites

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 by LAs
Link to post
Share on other sites

 

 

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 post
Share on other sites
  • 4 weeks later...

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 post
Share on other sites

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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...