Order by numbers

[argrafic]

Hello,

 

I have a problem when ordering by numbers.

 

My SQL statment is ORDER by titulo ASC, and titulo is a varchar string, it has a text name followed by a number, example:

 

Spot 1

Spot 2

Spot 3

...

Spot 13

 

The output of my SQL statement orders the results like this:

Spot 1

Spot 10

Spot 11

Spot 12

Spot 13

Spot 2... etc.

 

Why?

 

And how can I order it in the proper way? 1 followed by 2, 3, 4... 10, 11, 12, 13?

 

Thanks!!

[roopurt18]

It does this because it is sorting them as strings.  As a string, 10 comes before 2.

 

You'll have to perform some data manipulation in the query, which could have performance issues depending on the size of the data you are querying.  How many records are you dealing with?  How many records are you expecting to deal with in the future?  And does every single text string begin with 'Spot '?  If every column starts with 'Spot ', why put it there in the first place when you can just add it back in with code.

[resago]

that or make your numbers like 01 02 03 04 etc..

 

[hitman6003]

Definantly a workaround (read as "hack"), but just add 0 to the field...

 

SELECT * FROM table_name ORDER BY name + 0

 

There is a significant performance hit here because mysql is having to convert the field to a number data type and then order each one...so every time a query is done it has to do two table scans and a filesort no matter how small the data set you're returning is.  It probably won't be noticeable if you have a small number of rows, but once you get above a couple hundred you'll notice a performance degradation.

[argrafic]

I have the "Spot" title to go along with the number because in the same table (it's of videos) there is another category "Cápsulas".

 

But reading to your replys i think I will add 2 columns, one with the type and the other with the number. That should work, right?

[roopurt18]

Yes, that should make it easier.  When you pull the columns you can combine them with the CONCAT MySQL function:

CONCAT(col1, col2) AS 'displayString'

 

However, if you end up with lots of rows like this:

spot    -- 1

spot    -- 2

item    -- 1

spot    -- 3

item    -- 2

 

Then you might want to consider another table that holds:

spot

item

 

With IDs that you can refer to in the original table.

 

I hope that makes sense.