CODEWITHSUNDEEP
mysqlsql-order-by
Roy
Roy

Reputation: 4464

MySQL order by ABS if numeric

I need to order some information with order by in a MySQL query. Is there a possibility to use the ABS function only if the first char is numeric?

Pseudo example:

SELECT * FROM table 
ORDER BY
(( name[0] = numeric ) ? ABS(name) : name)
ASC

Thanks for the answers so far, tried a lot with your answers but I don't get the result I want.

The current code:

<?php
$filters = $this->db->query("
    SELECT
        tb_spec_feature.feature_id,
        tb_spec_feature.name
    FROM
        tb_spec_feature
    WHERE
        filter=1
");
$filters = $filters->result_array();
foreach($filters as $key=>$filter)
{
    $filters_sub = $this->db->query("
        SELECT DISTINCT
            tb_spec_value.value_id,
            tb_spec_value.name as value_name,
            tb_spec_sign.name as sign_name
        FROM
            tb_spec_fk
        INNER JOIN
            tb_spec_value
        ON
            tb_spec_value.value_id = tb_spec_fk.value
        LEFT JOIN
            tb_spec_sign
        ON
            tb_spec_sign.sign_id = tb_spec_fk.sign
        WHERE
            feature=".$this->db->escape($filter['feature_id'])."
        ORDER BY
            ABS(tb_spec_value.name) ASC
    ");
    $filters[$key]['values'] = $filters_sub->result_array();
}
?>

This will output (tb_spec_feature.name and then the tb_spec_value.name):

Screen inches

Resolution

Operating system

As you can see the inches and resolutions are okay, but the operating systems aren't ordered well. If I remove the ABS the result will be:

Screen inches

Resolution

Operating system

As you can see now, the operating systems are ordered well but the inches and resolutions not.. I hope somebody can help me out :)

Upvotes: 3

Views: 7075

Answers (4)

Saic Siquot
Saic Siquot

Reputation: 6513

This code is the MySql way of your question:

SELECT * FROM table 
ORDER BY
CASE WHEN LEFT(name, 1)>='0' AND LEFT(name, 1)<='9' THEN ABS(name) 
     ELSE name 
     END ASC

(Edit)
But according to comments, is better for you:

ORDER BY ABS(name), name

That works in this way:
If ABS() can be applied to 'name' the numeric resulting value will be ordered, but in the opposite case, if ABS(name) evaluates to null (because a non numeric has no abs value) then the second field (name) on order by, is which is relevant to the sort.

Upvotes: 4

Roy
Roy

Reputation: 4464

Got it! But, not with SQL.. I removed the ABS from the query and while handeling the query I use this:

<?
function order_it($a,$b)
{
    return $a["value_name"] - $b["value_name"];
}

foreach($filters as $key=>$filter)
{
    if(isset($filter['values'][0]['value_name'][0]) AND is_numeric($filter['values'][0]['value_name'][0]))
    {
        usort($filter['values'],'order_it');
    }
    foreach($filter['values'] as $item)
    {
        echo $item['value_name'].'<br />';
    }
}
?>

Now everything is ordered correctly :)

Upvotes: 0

keymone
keymone

Reputation: 8104

you could use convert function:

SELECT * FROM table ORDER BY CONVERT(name, SIGNED)

that will convert all non-numerics to 0 so you can safely pass it's result to functions that assume integer arguments:

ABS(CONVERT(name, SIGNED))

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172548

Try this:-

CASE WHEN LEFT(name[0], 1) LIKE [0-9] then ABS(name) ELSE name END

Assuming name as numeric

Upvotes: 0

Related Questions