Using CAST() with MAX()/MIN()

Question

I have some street numbers stored as VARCHARs but I want to sort them where possible as INTs. This syntax is accepted but produces alpha sorted sets like max = 53, min = 1203

SELECT  `StreetName`, 
CAST( MIN( `StreetNumber` ) AS UNSIGNED)  as min,
CAST( MAX( `StreetNumber` ) AS UNSIGNED)  as max
FROM  `tblAddToLotBridge` 
WHERE  `LotNumber` = '$item_lotnum'
AND `Primary_Secondary` = 0 
GROUP BY `StreetName`
ORDER BY `StreetName`

I tried nesting CAST() inside of MIN()/MAX() but that syntax got rejected.

What is the correct sytax to DWIM?

Solution

Is this what you tried?

SELECT  `StreetName`, 
MIN( CAST( `StreetNumber` AS UNSIGNED) ) as min,
MAX( CAST( `StreetNumber` AS UNSIGNED) ) as max
FROM  `tblAddToLotBridge` 
WHERE  `LotNumber` = '$item_lotnum'
AND `Primary_Secondary` = 0 
GROUP BY `StreetName`
ORDER BY `StreetName`