Jump to content

ORDER BY ASC - but not quite...


jaymc

Recommended Posts

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

[b]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[/b]

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

[b]
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[/b]

I assume the example is enough clarification. How do I go about this?
Link to comment
Share on other sites

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

[code=php:0]
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
[/code]

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

Of course, there are better ways to do this.
Link to comment
Share on other sites

[quote author=jaymc link=topic=112611.msg457360#msg457360 date=1161781269]
It does seem a bit far fetched, even though it works

Are their any alternitive ways to combat this issue?
[/quote]

You can sort it in PHP using [url=http://www.php.net/usort]usort()[/url]. It may be faster.
[code]
<?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);
?>
[/code]

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.
[code]
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;
[/code]

EDIT: modified usort snippet.
Link to comment
Share on other sites

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/manual/en/function.usort.php#55591
[quote=http://www.php.net/manual/en/function.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;
  }
}
?>
[/quote]
Link to comment
Share on other sites

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

[code]<?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);[/code]

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

here is an example of the array i will be using

[code]$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');[/code]

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?
Link to comment
Share on other sites

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
[code]
<?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);
?>
[/code]

//output
[code]
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
)
[/code]

EDIT: modified snippet
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.