Right Order_By (integer)

[eluser]R. Oerlemans[/eluser]
Hi There,

For a little order by I've bug, i get this as output:

Code:

> 1.5 Mb/s
> 10 Mb/s
> 120 Mb/s
> 20 Mb/s
> 25 Mb/s
> 3 Mb/s
> 30 Mb/s
> 4 Mb/s
> 5 Mb/s
> 50 Mb/s
> 60 Mb/s
> 8 Mb/s
> 90 Mb/s

But it needs to order by as total, so:

Code:

3 Mb/s
10 Mb/s
20 Mb/s
etc.
etc.

This is the code I am using:

Code:

$query_s = $this->db->group_by('snelheid')
                                            ->order_by("snelheid", "ASC")
                                                ->get('abonnementen')
                                                    ->result_array();

What can i do on this 'bug'?

Kind regards!

[eluser]jedd[/eluser]
There's the old + 0 trick, to force the field to think that it's numeric - I think that's the most common way of dealing with this.

Two things to note - you'll probably have to tell AR to not try to escape your order by clause (read up on this - I don't use AR so I'm not familiar with its foibles).

Secondly - check, using EXPLAIN perhaps (?), but certainly by reading up in the manual - but under some circumstances, usually aliased or some other thing I've forgotten, your INDEX columns can't use their index anymore. This might be irrelevant here, but if you see a big performance hit, remember this.

EDIT: Oh, hang on, are the 'MB/s' strings stored in the same column? If so, design your database properly, and try again. If not, then all well and good. I have no idea what happens when you start treating an alphanumeric field, especially one that starts with a number, as a plain numeric value. Something for you to experiment with, huh. Oh, and if you do design again, measure things in bytes, and convert to human readable at the presentation stage, not at the storage stage.

[eluser]davidbehler[/eluser]
What are the values of your 'snelheid' column?
"3 Mb/s" or just 3?

If it's the later, then you shouldn't have that problem.
If it's the first, then you could try this:

Code:

$query_s = $this->db->group_by('snelheid')
                                            ->order_by("cast(substring_index(test, ' Mb/s', 1) as signed)", "ASC")
                                                ->get('abonnementen')
                                                    ->result_array();

[eluser]R. Oerlemans[/eluser]
[quote author="jedd" date="1254072339"]There's the old + 0 trick, to force the field to think that it's numeric - I think that's the most common way of dealing with this.

Two things to note - you'll probably have to tell AR to not try to escape your order by clause (read up on this - I don't use AR so I'm not familiar with its foibles).

Secondly - check, using EXPLAIN perhaps (?), but certainly by reading up in the manual - but under some circumstances, usually aliased or some other thing I've forgotten, your INDEX columns can't use their index anymore. This might be irrelevant here, but if you see a big performance hit, remember this.[/quote]
Thanks, how can i use the + 0 trick in this case, I can try that one. But don't know how to do this in AR.


[quote author="jedd" date="1254072339"]
EDIT: Oh, hang on, are the 'MB/s' strings stored in the same column? If so, design your database properly, and try again. If not, then all well and good. I have no idea what happens when you start treating an alphanumeric field, especially one that starts with a number, as a plain numeric value. Something for you to experiment with, huh. Oh, and if you do design again, measure things in bytes, and convert to human readable at the presentation stage, not at the storage stage.[/quote]

The 'MB/s' is just from the PHP (<li><a href="/abonnementen/snelheid/&lt;?= $speed['snelheid']; ?&gt;"><span>></span> &lt;?= $speed['snelheid']; ?&gt; Mb/s</a></li>).

[eluser]R. Oerlemans[/eluser]
[quote author="waldmeister" date="1254072715"]What are the values of your 'snelheid' column?
"3 Mb/s" or just 3?

If it's the later, then you shouldn't have that problem.
If it's the first, then you could try this:

Code:

$query_s = $this->db->group_by('snelheid')
                                            ->order_by("cast(substring_index(test, ' Mb/s', 1) as signed)", "ASC")
                                                ->get('abonnementen')
                                                    ->result_array();

[/quote]

It's just 3, wich code could i use now?

[eluser]davidbehler[/eluser]
Try

Code:

$query_s = $this->db->group_by('snelheid')
                                            ->order_by("snelheid + 0", "ASC")
                                                ->get('abonnementen')
                                                    ->result_array();

[eluser]R. Oerlemans[/eluser]
Yeah, that works. Thanks. :-)

[eluser]Johan André[/eluser]
Usually when I have rows like this:

Code:

name
----------
Pool 1
Pool 2
Pool 3
Pool 4
...
Pool 10
Pool 11

and sort them based on name they end up like Pool 1, Pool 10...
Sorting like this helps:

Code:

$this->select('*');
$this->from('table');
$this->order_by('LENGTH(name), name ASC');