Casting to a decimal in SQL

The max value in my table for measure_rate is 572. The below query returns that value.

SELECT cast(max(measure_rate) as decimal) 
FROM [SurgicalMeasures]
WHERE measure_rate != 'N/a'
      and zip_code = 21236 

But when I change the above query to the query below, it gives me 95 as the max number.

SELECT cast(max(measure_rate) as decimal) 
FROM [SurgicalMeasures]
WHERE measure_rate != 'N/a'

My question is if I know 572 is the max measure_rate why are these queries returning different results?

The data type of the measure_rate column is a varchar(50)

Answer

Try casting to decimal first, then take the max of that casted value:

select max(cast(measure_rate as decimal(10,2))) as max_rate
from [SurgicalMeasures]
where measure_rate != 'N/a';

The potential problem with your current approach is that if measure_rate be text, then it may not sort properly as a number. That is, the MAX() function may return an incorrect value.