Getting highest value from table

ollmorris

I have a table with various records which contain whole numbers. What I want to do is obtain the highest value from the table. I tried:

$max = mysql_fetch_array (mysql_query ("SELECT hits FROM counter ORDER BY hits DESC"));

but it does not give me the value. Any help would be gratelly appreciated.

Thanks

zonewatcher

As your sql statement use ...

select max(hits) as maxhits from counter

... then you access maxhits from the array

BSTRhino

If the query is going to be run a lot, I would change it to:

SELECT MAX(hits) FROM counter

This would be slightly faster, as the database would not have to sort the entire table just for one value.

ollmorris

The problem with max is that I have data in the database:

1, 7, 29

and it reports 7 being the largest value. However when I change the data to 01, 07, 29, 29 is reported as being the largest value. Basically the amount of figures makes the difference.

Can ths be changed?

zonewatcher

What is the column type, character? If there is no reason to be character, you should consider changing to numeric ... much easier to perform calculations with numeric values.

Roger_Ramjet

Normally, I would say use Cast to coerce the string to a number and so enforce a numeric sort. But I keep getting pulled-up for posting solutions that are only compatyible with the latest versions of MySQL and PHP. So, I've got one that I am sure is version independent.

"SELECT Max(hits + 0) FROM counter"

MySQL dynamic typing responds by converting hits into an unsigned int, adding zero changes only the type. You should now get numeric comparison and sorting.