Help in query

[aman179]

I am trying to find out MAX ref number value of a column from a ledger table, where company are being selected from insurers table. It is working perfect.
But i need your comments on this query. Any other shorter way to do this?

$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>'; 

[crmalibu]

SELECT MAX(ref_no)
  FROM ledger
GROUP
    BY insurers

I'm making an assumption that the ledgers and insurers tables contain the same values for insurers field. Otherwise, a different query will be needed to give the functionality of the code you posted.

[aman179]

Thanks for your valuable reply brother. But your query is showing only MAX value of ref_no field, but I need both insurers & MAX values to display in a html table.

Ledger & insurers are different tables in a database. Insurers table has two columns (sr_no AUTO INCREMENT & insurers) & 9 rows. Populating a dropdown from insurers table & inserting values from dropdown to ledger table's insurers fields & 9 values are repeating in ledger table's insurers field. This query is working fine. But I wana know other methods to do this. I wana display it into html table's three fields like.

Sr No      Insurers         Max Value