order by issue

Hi,

the following query gets me the correct results however, The data stored makes the order by clause give me the output incorrectly.

the values to be returned are like this, for example.

10 The square
9 The square
7 The Square
My Business

I want to display it in numerical value and alphabetical order like this

7 The Square
9 The Square
10 The Square
My Business

So, numeric first and alphabetical afterwards.

Trouble is, 10 is seen as an earlier number than 7.

What should I be looking at when making the numerical item output erm, in numeric order - 7, 8, 9, 10?

here's the query so far

Code MySQL:

---

select id as sub_business_id
 , business as sub_business_name
 , parent_id as parent_id
 from businesses as b
 where (
         ( b.id = 123)
         or
         ( b.parent_id = 123 )
	   )
    order by sub_business_name

---

bazz

Quote:

---

Originally Posted by IBazz

So, numeric first and alphabetical afterwards.

---

dear bazz, how lovely to see you again after all these years :)

Code:

---

ORDER
    BY CASE WHEN 0 + SUBSTRING_INDEX(sub_business_name,' ',1) > 0
            THEN 'humpty'
            ELSE 'dumpty' END DESC
    , CASE WHEN 0 + SUBSTRING_INDEX(sub_business_name,' ',1) > 0     
            THEN RIGHT(CONCAT('00000000',
                    SUBSTRING_INDEX(sub_business_name,' ',1)),9)
            ELSE sub_business_name END

---

Hi rudy,

Thanks for the welcome back. you missed me a couple of times recently lol. been working away from the 'puter for a while and am a bit rusty. Still, even when sharp as a button, I never had the grasp of CASE.

Thanks for the code. I'll work through it to see why it does humpty and dumpty and come back if I can't get it.

bazz

So, is the first case statement basically saying;

ORDER
BY
# if the first char of the substring + 0 is greater than 0 put it in a group of 'humpty'
CASE WHEN 0 + SUBSTRING_INDEX(sub_business_name,' ',1) > 0
THEN 'humpty'
# else, put them in a group called 'dumpty'
ELSE 'dumpty' END DESC
# and then he got stuck. what does the next bit do, please?
, CASE WHEN 0 + SUBSTRING_INDEX(sub_business_name,' ',1) > 0
THEN RIGHT(CONCAT('00000000',
SUBSTRING_INDEX(sub_business_name,' ',1)),9)
ELSE sub_business_name END

Quote:

---

Originally Posted by IBazz

if the first char of the substring + 0 ...

---

no, it's not the first character, it's the substring up to the first space

Quote:

---

Originally Posted by IBazz

and then he got stuck.

---

the humpties come before the dumpties because of the DESC