Help Sorting!!

zero118

This is an excpert from a conversation I was having trying to figure out how to do this.... I hope you can understand what I'm needing but if not, ask me to explain.

Carebear 3.0 says:
$hpos = strpos('$partnum', '-');
$fsort = substr('$partnum', $hpos);
This is so that I can find a hyphen in string then strip the string down to just the number after the hyphen. The only problem is that I need to use this new variable to sort the results of a query. But the $partnum is a result of the query itself.

Carebear 3.0 says:
I'm going to have to use two queries aren't I

Carebear 3.0 says:
But there's even a problem doing that... I have to sort by the fields inside the table. The only way to actually sort would be to remove everything to the hyphen inside the table itself, a permenant thing.

NogDog

If using MySQL (don't know how compatible this is with other DBMS's):

SELECT `col1`, `col2`, `col3`, SUBSTRING_INDEX(`col1`, '-', -1) AS `sort_num` 
FROM `table_name` ORDER BY `sort_num`

zero118

That works perfectly except that in some of them we have multiple hyphens. Is there a way to expand on that and only find the first hyphen?

NogDog

Instead of the SUBSTRING_INDEX() expression, you could try:

SUBSTRING(`col`, LOCATE('-', `col`) + 1) AS `sort_num`

zero118

One step closer =-) Thanks for your help but one last thing...

We have numbers that go pretty high. Is there a way to expand further in the following manner?

when the results show right now they are:
1
10
100
2
20
200

Can we take those and sort them properly like:
1
2
10
20
100
200

I really appreciate your help. It's been very clear.

suntra

SELECT field FROM table ORDER BY field ASC

Unless I'm wrong, if a field is defined as an integer (or long or double ?) MySQL will sort it numerically... so 1 < 2 < 5 < 10 < 50 < 100 < 1000 ...

zero118

It's a varchar. Such as "KM-23" which is why I needed to find the hyphen then start sorting after it.

suntra

Well, maybe...

... ORDER BY sort_sum ASC

... ORDER BY floor(sort_sum) ASC

zero118

Floor might have worked but it won't accept the AS sort_sum

Here's my query so far.

SELECT name, id, desc, partnum, SUBSTRING(partnum, LOCATE('-', partnum) + 1) AS sort_part FROM $parts_database_table WHERE name=\"$n\" ORDER BY sort_part ASC

This does work BUT still sorts it like
1
10
100
2
20
etc

SO how do we take the working query and make is sort normally like the following even though it's a varchar?
1
2
10
20

bradgrafelman

Try changing this:

SUBSTRING(`partnum`, LOCATE('-', `partnum`) + 1) AS `sort_part`

to this:

CAST( (SUBSTRING(`partnum`, LOCATE('-', `partnum`) + 1) AS SIGNED) AS `sort_part`

zero118

That gave me this

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS SIGNED) AS sort_part FROM parts WHERE name="Product' at line 1

EDIT
I found the problem in that line and it works great 🙂 Thanks!