argrafic Posted January 25, 2008 Share Posted January 25, 2008 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!! Link to comment https://forums.phpfreaks.com/topic/87841-order-by-numbers/ Share on other sites More sharing options...
roopurt18 Posted January 26, 2008 Share Posted January 26, 2008 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. Link to comment https://forums.phpfreaks.com/topic/87841-order-by-numbers/#findComment-449383 Share on other sites More sharing options...
resago Posted January 26, 2008 Share Posted January 26, 2008 that or make your numbers like 01 02 03 04 etc.. Link to comment https://forums.phpfreaks.com/topic/87841-order-by-numbers/#findComment-449385 Share on other sites More sharing options...
hitman6003 Posted January 26, 2008 Share Posted January 26, 2008 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. Link to comment https://forums.phpfreaks.com/topic/87841-order-by-numbers/#findComment-449499 Share on other sites More sharing options...
argrafic Posted January 28, 2008 Author Share Posted January 28, 2008 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? Link to comment https://forums.phpfreaks.com/topic/87841-order-by-numbers/#findComment-451307 Share on other sites More sharing options...
roopurt18 Posted January 28, 2008 Share Posted January 28, 2008 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. Link to comment https://forums.phpfreaks.com/topic/87841-order-by-numbers/#findComment-451355 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.