Jump to content


Photo

ORDER BY ASC - but not quite...


  • Please log in to reply
9 replies to this topic

#1 jaymc

jaymc
  • Members
  • PipPipPip
  • Advanced Member
  • 1,521 posts
  • LocationLiverpool

Posted 25 October 2006 - 12:16 AM

Take a look at the following data which is displayed in the ASC order

Bounce To The Bass 13
Bounce To The Bass 16
Bounce To The Bass 5
Bounce To The Bass 6
Bounce To The Bass 7
Bounce To The Bass 8
NRGetik Beatz Battle Round 1
NRGetik Beatz Battle Round 2


As you can see, it is officially, in ascending order. However, and I think it would be easier to just demonstrate this, I want it ordered/displayed like this


Bounce To The Bass 5
Bounce To The Bass 6
Bounce To The Bass 7
Bounce To The Bass 8
Bounce To The Bass 13
Bounce To The Bass 16
NRGetik Beatz Battle Round 1
NRGetik Beatz Battle Round 2


I assume the example is enough clarification. How do I go about this?

I would love to change the world, but they won't give me the source code

SEO Agency

#2 extrovertive

extrovertive
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 25 October 2006 - 06:26 AM

SELECT  REVERSE(TRIM(SUBSTR(REVERSE(title), 3))) as title, (TRIM(RIGHT(title, 2))+0) as track
FROM songtitle
ORDER By title, track

#3 jaymc

jaymc
  • Members
  • PipPipPip
  • Advanced Member
  • 1,521 posts
  • LocationLiverpool

Posted 25 October 2006 - 08:46 AM

I take it track = Bounce To The Bass 13 variables etc?
I would love to change the world, but they won't give me the source code

SEO Agency

#4 extrovertive

extrovertive
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 25 October 2006 - 09:16 AM

title is the whole name - i.e. "Bounce To The Bass 5"

I then truncate that to "Bounce To The Bass" - stripping off the number at the end.

So now,
track is just the track #
title is the name of the album

Example:
title = Bounce To The Bass
track = 5

"songtitle" is the name of the table (you can change it to whatever you want).

I had to split the title field into two alias columns to properly sort it.

But if you want them all in one field use, you can also use a subqeury

SELECT CONCAT(title, '  ', track) as title FROM
(
	SELECT  REVERSE(TRIM(SUBSTR(REVERSE(title), 3))) as title, (TRIM(RIGHT(title, 2))+0) as track
	FROM songtitle
	ORDER By title, track 
) as tracktable

Just change the table name "songtitle" to whatever you have.

Of course, there are better ways to do this.

#5 jaymc

jaymc
  • Members
  • PipPipPip
  • Advanced Member
  • 1,521 posts
  • LocationLiverpool

Posted 25 October 2006 - 01:01 PM

It does seem a bit far fetched, even though it works

Are their any alternitive ways to combat this issue?
I would love to change the world, but they won't give me the source code

SEO Agency

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 October 2006 - 03:42 PM

It does seem a bit far fetched, even though it works

Are their any alternitive ways to combat this issue?


You can sort it in PHP using usort(). It may be faster.
<?php
function sort_text($a, $b)
{
    $a_parts = preg_split('/([0-9]+)$/', $a, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);
    $b_parts = preg_split('/([0-9]+)$/', $b, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);

    if (0 == ($cmp = strcmp($a_parts[0], $b_parts[0])))
    {
        $a_num = isset($a_parts[1])? (int)$a_parts[1]: -1;
        $b_num = isset($b_parts[1])? (int)$b_parts[1]: -1;
        if ($a_num == $b_num)
        {
            return 0;
        }
        return ($a_num < $b_num)? -1: 1;
    }
    else
    {
        return $cmp;
    }
}
$a = array('b', 'b 01', 'a 1', 'a 31', 'a 30', 'a 15', 'a 10', 'a 01');
print_r($a);

usort($a, 'sort_text');
print_r($a);
?>

I felt that if I was going to provide an alternate query to do the ordering that it should be comprehensive. This will sort any text that may end with digits. Sorting based on the preceding text and then the digits.

It should be very accurate but I would recommend doing the sorting in PHP because it's easier to understand , modify and I'd have to assume faster. I'd be surprised if the sorting in PHP wasn't faster than this query.
SELECT
*
FROM
tablename
ORDER BY
TRIM(TRAILING REVERSE(TRIM(LEADING '-1' FROM CONCAT('-1', REVERSE(columnname)) + 0)) FROM columname),
IF(columname REGEXP('[0-9]+$'), REVERSE(TRIM(LEADING '-1' FROM CONCAT('-1', REVERSE(columnname)) + 0)), -1) + 0;

EDIT: modified usort snippet.

#7 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 October 2006 - 07:31 PM

The linked/quoted usort snippet goes a step further and will sort throughout the text numerically when a number is encoutered anywhere in the text.

You may want to remove or comment the first 2 lines of the cmp function however.

http://www.php.net/m...usort.php#55591

function cmp($a,$b){
  list($a)=explode(".",$a);
  list($b)=explode(".",$b);
  $s=0;
  for($i=0;$i<=strlen($a);++$i){
    if(is_numeric($a[$i])&&is_numeric($b[$i])){
        $n=1;
        $anum=$bnum=0;
        while((is_numeric($a[$i])||is_numeric($b[$i]))&&
                  (strlen($a)>=$i||strlen($b)>=$i)){
          if(is_numeric($a[$i])&&$i<=strlen($a)) $anum=$a[$i]+$anum*$n;
          if(is_numeric($b[$i])&&$i<=strlen($b)) $bnum=$b[$i]+$bnum*$n;
          $n*=10;
          ++$i;
        }
        if($n>1) --$i;
        if($anum!=$bnum) return $anum<$bnum?-1:1;
    }elseif($a[$i]!=$b[$i]) return $a[$i]<$b[$i]?-1:1;
  }
}
?>



#8 jaymc

jaymc
  • Members
  • PipPipPip
  • Advanced Member
  • 1,521 posts
  • LocationLiverpool

Posted 26 October 2006 - 11:46 AM

Ive got this example working fine, still got a problem though which i'll explain later

<?php
function sort_text($a, $b)
{
    $a_parts = preg_split('/([0-9]+)$/', $a, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);
    $b_parts = preg_split('/([0-9]+)$/', $b, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);

    if (0 == ($cmp = strcmp($a_parts[0], $b_parts[0])))
    {
        $a_num = isset($a_parts[1])? (int)$a_parts[1]: -1;
        $b_num = isset($b_parts[1])? (int)$b_parts[1]: -1;
        if ($a_num == $b_num)
        {
            return 0;
        }
        return ($a_num < $b_num)? -1: 1;
    }
    else
    {
        return $cmp;
    }
}
$a = array('b', 'b 01', 'a 1', 'a 31', 'a 30', 'a 15', 'a 10', 'a 01');
print_r($a);

That is working fine, however, its not doing exactly what I want

here is an example of the array i will be using

$a = array('b|34', 'b 01|65', 'a 1|435', 'a 31|345', 'a 30|43', 'a 15|345', 'a 10|224', 'a 01|678');

I need that sorting like the function above is doing, but, not to display anything on the right hand side of the |

However, once the sort has been completed, I will need the numbers on the right and side of the | for each element thats been sorted.

The sort will be echoed as html as a href link you see, and the numbers on the right are ID's of which I need. The text on the left of the | is just the URL text link

Hope ive explained that well enough, any ideas?
I would love to change the world, but they won't give me the source code

SEO Agency

#9 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 26 October 2006 - 01:13 PM

If you're only asking how to modify the function to do the sort based on the text on the left of the "|", then the following should work
<?php
function sort_text($a, $b)
{
    $a_parts = preg_split('/(?:([0-9]+)?\|[0-9]+)$/', $a, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);
    $b_parts = preg_split('/(?:([0-9]+)?\|[0-9]+)$/', $b, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);

    if (0 == ($cmp = strcmp(trim($a_parts[0]), trim($b_parts[0]))))
    {
        $a_num = isset($a_parts[1])? (int)$a_parts[1]: -1;
        $b_num = isset($b_parts[1])? (int)$b_parts[1]: -1;

        if ($a_num == $b_num)
        {
            return 0;
        }
        return ($a_num < $b_num)? -1: 1;
    }
    else
    {
        return $cmp;
    }
}
$a = array('b|34', 'b 01|65', 'a 1|435', 'a 31|345', 'a 30|43', 'a 15|345', 'a 10|224', 'a 01|678');
print_r($a);

usort($a, 'sort_text');
print_r($a);
?>

//output
Array
(
    [0] => b|34
    [1] => b 01|65
    [2] => a 1|435
    [3] => a 31|345
    [4] => a 30|43
    [5] => a 15|345
    [6] => a 10|224
    [7] => a 01|678
)
Array
(
    [0] => a 01|678
    [1] => a 1|435
    [2] => a 10|224
    [3] => a 15|345
    [4] => a 30|43
    [5] => a 31|345
    [6] => b|34
    [7] => b 01|65
)

EDIT: modified snippet

#10 jaymc

jaymc
  • Members
  • PipPipPip
  • Advanced Member
  • 1,521 posts
  • LocationLiverpool

Posted 26 October 2006 - 01:41 PM

Worked perfect!

Thanks!!!!
I would love to change the world, but they won't give me the source code

SEO Agency




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users