SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: MySql MAX Query

  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySql MAX Query

    I have crated a table named "ledger" containing 26 fields. In this table "ref_no" is a field in which i am entering data through web forms. In the "ref_no" field i have data like

    Code:
    BAL/01/2009
    BAL/02/2009 
    (& so on)
    NIC/01/2009
    NIC/02/2009
    (& so on)
    OIC/01/2009
    OIC/02/2009
    OIC/03/2009
    (& so on)
    where BAL,NIC & OIC short forms of names & 01,02 & 03 are incremending numbers & 2009 represents current year. Now I need to show MAX ref_no for each company for that I have used following code

    PHP Code:
    $query "SELECT insurers FROM insurers";
    $result mysql_query$query );
        if (!
    $result){
        die (
    "Could not query the database: <br />"mysql_error( ));
        }
        echo 
    '<table border=1> <tr>';
        
    $i 0;
        while (
    $ins mysql_fetch_array($result)) 
        {
        
    $i++;
            
    $query1 'SELECT MAX(ref_no) FROM ledger WHERE insurers="' $ins['insurers'] . '"';        
            
    $result1 mysql_query$query1 );
                if (!
    $result1)
                {
                die (
    "Could not query the database: <br />"mysql_error( ));
                }
                    while(
    $in mysql_fetch_array($result1))
                    {
                    echo 
    '<td>'    $i '</td><td> Recent Ref # in <i> ' $ins["insurers"] . '</i></td><td>' $in[0] . '</td></tr>';
                    }
        }
        echo 
    '</table>';    
        
    $query2 'SELECT MAX(bill_no) FROM ledger';        
            
    $result2 mysql_query$query2 );
                if (!
    $result2)
                {
                die (
    "Could not query the database: <br />"mysql_error( ));
                }
                    while(
    $in1 mysql_fetch_array($result2))
                    {
                    echo 
    'Last Generated Bill No is ' $in1[0] ;
                    } 
    Everything was working perfectly fine till the ref no field reached BAL/99/2009. Now last generated ref_no in the column is BAL/101/2009 & NIC/133/2009. But when I use this query its not showing record after NIC/99/2009 & BAL/99/2009. Same prob in bill_no field it is strucked on 99/2009 whereas last generated bill_no of 534/2009. I think the prob is with MySql "MAX" query. Experts please help me with this prob...

    Waiting reply.

    Aman

  2. #2
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is because MySQL returns a maximum string value. This is sorted as an alpha-numerical string. What you want is a natural sort. Not implemented by MySQL.

    A workaround would be to use strings to split the string into 3 chunks (for example: 'BAL/01/2009' => 'BAL', '01', '2009') then cast the number and year into integer and then order by those 3 chunks separately and select the top row.

    Another, simpler, solution is to pad the number with zeroes so that all strings are equal lengths.

    Also, this is wrong forum. It should be in SQL.

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by decowski View Post
    This is because MySQL returns a maximum string value. This is sorted as an alpha-numerical string. What you want is a natural sort. Not implemented by MySQL.

    A workaround would be to use strings to split the string into 3 chunks (for example: 'BAL/01/2009' => 'BAL', '01', '2009') then cast the number and year into integer and then order by those 3 chunks separately and select the top row.

    Another, simpler, solution is to pad the number with zeroes so that all strings are equal lengths.

    Also, this is wrong forum. It should be in SQL.
    thanks fr your reply bro.. sorry for posting this thread in wrong section ...


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
  •