com wrote:
ORDER BY 1 * field
In a DB I am managing there is a varchar field that contains integers or fractions as strings. As an example it contains strings like "1", "2" ,"100" as well as "1/2", "1/4" or "3/4". I would like to know if there is any way to sort the output of a query not by the string value (in such a case 1 comes before 1/2) but by its numeric value. Thanks, M....
In a DB I am managing there is a varchar field that contains integers
or fractions as strings. As an example it contains strings like "1",
"2" ,"100" as well as "1/2", "1/4" or "3/4".
I would like to know if there is any way to sort the output of a query
not by the string value (in such a case 1 comes before 1/2) but by its
numeric value.
Thanks,
M.
com wrote:
ORDER BY 1 * field
strawberry ha scritto:
>
> ORDER BY 1 * field[/ref]
It does not work:
SELECT 1*field,filed FROM mytable GROUP BY field
outputs:
1*field field
0 0
1 1
1 1/14
1 1/2
1 1/4
10 10
100 100
1000 1000
....
is there any way to have a floating point multiplication? I tried
1.0*field bu it does not work as well
com wrote:
> >
> > ORDER BY 1 * field[/ref]
>
> It does not work:
>
> SELECT 1*field,filed FROM mytable GROUP BY field
>
> outputs:
>
> 1*field field
> 0 0
> 1 1
> 1 1/14
> 1 1/2
> 1 1/4
> 10 10
> 100 100
> 1000 1000
> ...
>
> is there any way to have a floating point multiplication? I tried
> 1.0*field bu it does not work as well[/ref]
In that case I think you have to do it the long way, i.e. for each
fraction divide the numerator by the divisor - something like this
(incomplete and unchecked):
SELECT ROUND(numerator/divisor,2) metric, fraction FROM (
SELECT
SUBSTR(fraction,1,(INSTR(fraction,'/')-1)) AS numerator,
SUBSTR(fraction,((LENGTH(fraction))-(INSTR(fraction,'/')-2)),LENGTH(fraction))
AS divisor,
fraction
FROM fractions
) t1;
Note: The subquery is just for clarity.
If this is just a display issue, then I'd seriously consider storing
the fractions as decimals and then using a bit of php to display them
in an imperial/'english') format.
strawberry ha scritto:
Ok it starts to be clear for me. It needs some work since no every
field presents the '/' but I hope to be able to solve this.
Thanks for your precious help.
When my query will be ready I will post it just to give you the idea on
how a not so well implemented DB needs so much complexity :)
Unfortunately, I can not change that field!
Thanks again
com ha scritto:
Ok the nearly final query is a mix of your contribution and material I
found on
http://www.kanolife.com/escape/2006/03/mysql-string-splitter.html
There is only a minor open issue I discuss at the end.
Moreover there was an additional problem that some number is actually
inserted as decimal but using the ',' as a decimal separator; a simple
replace in that case can suffice:
SELECT ROUND(numeratore/(divisore+1),2) as result,nominale as orig FROM
(SELECT replace(substring_index(nominale, '/', 1),',','.') as
numeratore, replace(substring(substring_index(nominale, '/',
2),length(substring_index(nominale, '/', 2 - 1)) + 1), '/', '') as
divisore,nominale FROM globalmoneta)t1 GROUP BY result ORDER BY result;
+---------+----------+
| result | orig |
+---------+----------+
| 0.00 | 0 |
| 0.05 | 0,05 |
| 0.07 | 1/14 |
| 0.10 | 0,10 |
| 0.20 | 1/4 |
| 0.33 | 1/2 |
| 0.50 | 0,50 |
| 1.00 | 1 |
| 2.00 | 2 |
| 2.50 | 2,5 |
| 3.00 | 3 |
| 4.00 | 4 |
| 5.00 | 5 |
| 6.00 | 6 |
| 8.00 | 8 |
| 10.00 | 10 |
| 12.00 | 12 |
| 15.00 | 15 |
| 20.00 | 20 |
| 24.00 | 24 |
| 25.00 | 25 |
| 30.00 | 30 |
| 31.00 | 31 |
| 40.00 | 40 |
| 48.00 | 48 |
| 50.00 | 50 |
| 60.00 | 60 |
| 80.00 | 80 |
| 96.00 | 96 |
| 100.00 | 100 |
| 200.00 | 200 |
| 500.00 | 500 |
| 1000.00 | 1000 |
+---------+----------+
The open issue: when there is not the '/' char in the field the divisor
field is computed as NULL. This is not good when computing the
division. I solved this adding a 1 to the divisor. In such a case the
result is ok when no fractions but is slightly wrong when a fraction is
computed i.e. 1/2 is computed as 0.33. Not a critical issue, anyway I
am wondering if there is a more exact solution.
Thanks again for your precious help!
com wrote:
>
> Ok the nearly final query is a mix of your contribution and material I
> found on
>
> http://www.kanolife.com/escape/2006/03/mysql-string-splitter.html
>
> There is only a minor open issue I discuss at the end.
>
> Moreover there was an additional problem that some number is actually
> inserted as decimal but using the ',' as a decimal separator; a simple
> replace in that case can suffice:
>
> SELECT ROUND(numeratore/(divisore+1),2) as result,nominale as orig FROM
> (SELECT replace(substring_index(nominale, '/', 1),',','.') as
> numeratore, replace(substring(substring_index(nominale, '/',
> 2),length(substring_index(nominale, '/', 2 - 1)) + 1), '/', '') as
> divisore,nominale FROM globalmoneta)t1 GROUP BY result ORDER BY result;
>
> +---------+----------+
> | result | orig |
> +---------+----------+
> | 0.00 | 0 |
> | 0.05 | 0,05 |
> | 0.07 | 1/14 |
> | 0.10 | 0,10 |
> | 0.20 | 1/4 |
> | 0.33 | 1/2 |
> | 0.50 | 0,50 |
> | 1.00 | 1 |
> | 2.00 | 2 |
> | 2.50 | 2,5 |
> | 3.00 | 3 |
> | 4.00 | 4 |
> | 5.00 | 5 |
> | 6.00 | 6 |
> | 8.00 | 8 |
> | 10.00 | 10 |
> | 12.00 | 12 |
> | 15.00 | 15 |
> | 20.00 | 20 |
> | 24.00 | 24 |
> | 25.00 | 25 |
> | 30.00 | 30 |
> | 31.00 | 31 |
> | 40.00 | 40 |
> | 48.00 | 48 |
> | 50.00 | 50 |
> | 60.00 | 60 |
> | 80.00 | 80 |
> | 96.00 | 96 |
> | 100.00 | 100 |
> | 200.00 | 200 |
> | 500.00 | 500 |
> | 1000.00 | 1000 |
> +---------+----------+
>
> The open issue: when there is not the '/' char in the field the divisor
> field is computed as NULL. This is not good when computing the
> division. I solved this adding a 1 to the divisor. In such a case the
> result is ok when no fractions but is slightly wrong when a fraction is
> computed i.e. 1/2 is computed as 0.33. Not a critical issue, anyway I
> am wondering if there is a more exact solution.
>
> Thanks again for your precious help![/ref]
How about using the IFNULL function to return either the value or a 1
if the expression is null.
Seems like this is exactly what the function was designed for.
I was not aware about IFNULL!
Anyway I solved in a mathematical/logical way:
SELECT ROUND(numeratore/(divisore+1* NOT divisore),2)
Great!
| 0.20 | 1/4 |
| 0.33 | 1/2 |
I'm no mathematician, but...!
strawberry ha scritto:
That was the open issue... now closed :)
com wrote:
>
> That was the open issue... now closed :)[/ref]
Oops, I should've read the thread more carefully!
Bookmarks