blasteralfred Ψ blasteralfred Ψ - 6 months ago 19
MySQL Question

Selecting MAX number from MySQL table not working - PHP

I have a table like below (a piece);

| Sl | NUMBER | Name |
----------------------
| 7 | 00007 | Name |
----------------------
| 8 | 00008 | Name |
----------------------
| 9 | 00009 | Name |
----------------------
| 10 | 00010 | Name |
----------------------
| 11 | 00011 | Name |
----------------------
| 12 | 00012 | Name |
----------------------
| 13 | 00013 | Name |
----------------------


and I have mysql statement like this;

$con = mysql_connect("localhost","root","password");
if (!$con){
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $con);
$query = 'SELECT MAX(NUMBER) FROM tablename';//varchar 15 entry
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$maxnumber = $row['MAX(NUMBER)'];
}
echo $maxnumber;


But it returns 9 instead of 13. Why?

Answer

Use this query

SELECT 
    *
FROM tablename AS t
INNER JOIN (SELECT MAX(SL) AS SL FROM tablename) AS r ON r.SL = t.SL

SQL Fiddle Demo