php mysql sort result by value of field using position of substring

Question

I have field name "codehead" whose values are like

"53/066/10" 
"54/066/05" 
"56/066/09" 
"52/069/15" 
"53/069/02"
"67/069/02"
"00/020/80"
"00/020/98"

I want the results to be in following order

"00/020/80"
"00/020/98"
"53/066/05"
"53/066/10"
"54/066/09"
"52/069/15" 
"53/069/02"
"67/069/02"

I have tried query like

$data= mysql_query("select codehead,sign, SUM(amt) as amt from pm where month='$pmmonth' and rc='R' GROUP BY substr(codehead,4,3) ASC,substr(codehead,7,2) ASC,  sign ")

but could not get complete result.

Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 29 '15 at 19:03

spencer7593 · Answer 1 · 2015-05-29T18:30:05.890

This expression:

substr(codehead,7,2)

Is returning a forward slash and a digit. Looks like you wanted to start at position 8.

But that won't satisfy the order shown, the fifth row has 09, and that would come before 10 on the preceding row. The desired result shown makes it look like the result is also ordered on LEFT(codehead,2)

 GROUP BY codehead, `sign`
 ORDER BY substr(codehead,4,3)  ASC
        , LEFT(codehead,2)      ASC
        , substr(codehead,8,2)  ASC

NOTE: In some future release of MySQL, the non-Standard MysQL extension of the GROUP BY to imply an ORDER BY may be removed.

I'm very suspicious of the GROUP BY on just the substrings. That's valid SQL to do that, I just question how you want to handle codehead values that match on the substrings, but is different in some other place... especially given that you're returning codehead in the SELECT list.

The normative pattern is to GROUP BY expressions in the SELECT list.

absolutely right , it clicked , working fine as I desired. Your apprehension about fifith row "09" would come before "10" not happened in this case, I just want it this way only. Thanks a lot — MH_007, May 30 '15 at 14:18

score 0 · Answer 2 · answered May 29 '15 at 18:32

You can do something like this:

SELECT codehead,
  substring(codehead,1,2) as 'one',
  substring(codehead,4,3) as 'two',
  substring(codehead,8,2) as 'three'
FROM foo
ORDER BY `two`, `one`, `three` ASC

SQLfiddle

and it will work, but it's going to have subpar performance because it's impossible to use indexes for that sort.

To address this you could add a new field to the table, eg sortkey, and insert a string in the format $two.$one.$three for each row. Then you can index and sort on that. Better yet, split all three values into their own fields and then you can index and sort however you want.

php mysql sort result by value of field using position of substring

2 Answers2