Sort by Alphabetical and numeric

[datoshway]

Hey guys.  I have results like A1, B4, C19, C35, d22, etc etc.  How can I have my results just sort in order of first letter then number so all the A's would be grouped etc etc.

 

[fugix]

okay we are going to try this approach

$sql = "SELECT * FROM table_name ORDER BY column_name + 0, column_name";

where "table_name" and "column_name" will be the values of your table and field that you want to order by

[Skylight_lady]

One way is to break into two field for sorting, like:

order by substring(cols,1,1), lpad(substring(cols,2),10,0)
                        ^ assuming only prefix single char 
                              ^ lpad like 0000000001

 

ORDER BY my_field

 

This will do the work. ORDER BY sorts numbers, strings, dates, etc in natural way.

 

ORDER BY LEFT(fld, 1), CAST(SUBSTRING(fld, 1) AS SIGNED)

 

 

[datoshway]

Fugix, thank you thank you thank you!  Worked perfect.  Simple solution.  Love it.  Thanks again.

 

[fugix]

Fugix, thank you thank you thank you!  Worked perfect.  Simple solution.  Love it.  Thanks again.

no problem, it's a convenient trick, glad I could help you out with this