Professional Web Applications Themes

Sorting fractions - MySQL

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....

  1. #1

    Default Sorting fractions

    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.

    numismatica@gmail.com Guest

  2. #2

    Default Re: Sorting fractions


    com wrote: 

    ORDER BY 1 * field

    strawberry Guest

  3. #3

    Default Re: Sorting fractions


    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

    massimo.bertozzi@gmail.com Guest

  4. #4

    Default Re: Sorting fractions


    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 Guest

  5. #5

    Default Re: Sorting fractions


    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

    numismatica@gmail.com Guest

  6. #6

    Default Re: Sorting fractions

    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!

    numismatica@gmail.com Guest

  7. #7

    Default Re: Sorting fractions


    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.

    Captain Guest

  8. #8

    Default Re: Sorting fractions

    I was not aware about IFNULL!

    Anyway I solved in a mathematical/logical way:

    SELECT ROUND(numeratore/(divisore+1* NOT divisore),2)

    Great!

    massimo.bertozzi@gmail.com Guest

  9. #9

    Default Re: Sorting fractions

    | 0.20 | 1/4 |
    | 0.33 | 1/2 |

    I'm no mathematician, but...!

    strawberry Guest

  10. #10

    Default Re: Sorting fractions


    strawberry ha scritto:
     

    That was the open issue... now closed :)

    massimo.bertozzi@gmail.com Guest

  11. #11

    Default Re: Sorting fractions


    com wrote: 
    >
    > That was the open issue... now closed :)[/ref]

    Oops, I should've read the thread more carefully!

    strawberry Guest

Similar Threads

  1. Simulating Fractions in Futura
    By cj_cik@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 3
    Last Post: November 14th, 12:28 PM
  2. Fractions.
    By Richard_collett@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 7
    Last Post: August 21st, 02:29 AM
  3. Fractions of a pixel?
    By Frunobulax in forum Macromedia Flash
    Replies: 5
    Last Post: May 17th, 03:31 PM
  4. cmyk fractions
    By Gethin_Coles@adobeforums.com in forum Adobe Illustrator Windows
    Replies: 7
    Last Post: August 9th, 06:16 PM
  5. FRACTIONS?? How can flash work them out??
    By Uni_student_uk webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: January 22nd, 09:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139