[Resolved] ORDER BY name ASC - works from 1-9 problem at 10

helz

if i have these rows in the database:

week 1
week 2
week 3
all the way to week 10
but when it does week 10 it puts it at the top when i order the query results.

anyone know a way to keep it at the bottom?

tekky

maybe....

SELECT * FROM table WHERE x=y ORDER BY REPLACE(x,'week ', '') ASC

something like that should work.... I believe :p

helz

instead of the WHERE x=y, i have GROUP BY name
also, you got any extra info about the REPLACE() part for sql queries?

tekky

All the replace is doing is taking the "week " out of the column result you were originaly ordering by... leaving only the # to order by....

If that doesnt work... then well something more creative will be needed I guess...

ie:

instead of
week 1
week 2
week 3
week 10
week 11

you get
1
2
3
10
11

helz

well the problem is that its doing:

week 1
week 10
week 11
week 2
week 3

whereas i want it as

week 1
week 2
week 3
...
week 9
week 10
week 11

tekky

have you tried what I suggested? if so what were the results?

helz

query:
SELECT woid FROM $rnd1tbl GROUP BY woid ORDER BY REPLACE(x,'week ', '') ASC

get error:
wrong parameter count

tekky

Weird, I can do it using RIGHT(name, 2) but not using REPLACE....

the error is coming from this

REPLACE('string', 'search strnig', 'replace string') (i initially didnt have enough params provided)

helz

whats the x for?
does it need to have single quotes around it?

==correction==
is x the column name?

tekky

x was a column name example... same as y

I almost have it working where it wont matter the string size or the number size

ie:
week 12
week 12312312
week 1

would all be sorted

helz

i changed it to:
SELECT woid FROM $rnd1tbl GROUP BY woid ORDER BY REPLACE(woid,'week ', '') ASC

but its still doing:
week 1
week 10
week 2
week 3
week 4
....

tekky

working... but when using a "debugging" type method, the output I was expecting wasnt right... but its not failed to sort right yet....

SELECT * FROM testing
WHERE 1
ORDER BY MID(name, LOCATE( ' ', name), (LENGTH(name)-LOCATE(' ', name))) ASC

results:
week 1

week 2

week 3

week 10

week 11

week 123

week 999

helz

you can see it here:
http://wiganyac.hellrazer.net/index.php?module=wo
as you can see it puts week 10 at the top and then carries on...
(week 1 doesnt exist because the scores are lost)

tekky

which query is that? the MID one or the attempt at REPLACE?

helz

replace

tekky

the MID method worked for me... I'm still playing with simplifying it but anytime It goes simpler... it begins to sort with 1 10 11 2 3 4 method again

helz

i tested the mid one, same result as before

tekky

Originally posted by helz
i tested the mid one, same result as before

weird, it produced the results I pasted in the earlier post just fine....

helz

ill echo out the sql:
http://wiganyac.hellrazer.net/index.php?module=wo

tekky

bleh, further testing proved my MID one doesnt work....

week 1
week 2
week 3
week 9

week 7

week 10

week 11

week 123

week 49

week 97
week 999 :rolleyes: