I have dumped a file into a temp table and trying to pull records where the score is <= 100. The column has values for example, 3, 4.50, 10.02, 99.88,99,99, 100, 100.2, 100, 116, 116.44 etc. I need only 3, 4.50, 10.02,99.88,99,99,100. When I tried the below, it does not give the proper result. Could anyone advise?
SELECT CAST(sum AS numeric ),* FROM temp WHERE sum >= '100' ;
There are two issues here. First, you're using the >= operator instead of the <= operator. Second, sum seems to be a string column, so the comparison is performed lexicographically. You could apply the same casting you've applied in the select list and compare the value to a number literal 100, not a string literal '100':
SELECT CAST(sum AS numeric ),* FROM temp WHERE CAST(sum AS numeric) >= 100;