SELECT max(meta_value) FROM wp_postmeta WHERE meta_key=’price’… stops working when value is over 999

by

Overview: I am not trying to return a post. I simply want the single highest value for a particular meta_value across all posts… just the value itself.

Details: I have added a custom meta_key “price” to all my posts. The value is always an integer (no decimals or non-numeric characters). I am trying to do a query that returns the highest / largest / maximum meta_value associated with this particular meta_key.

Buggy Code

function max_meta_value(){
    global $wpdb;
    $query = "SELECT max(meta_value) FROM wp_postmeta WHERE meta_key='price'";
    $the_max = $wpdb->get_var($query);
    return $the_max;
}

Buggy Results: At first I thought the above code worked, because it does if all the meta_values are less than 999. I soon discovered that if the meta_value is greater than 999 then it is ignored. So really the code above is giving me the max(meta_value) for meta_values less than 1000.

Plea to Community: Obviously I do not know why it fails, but I have a feeling that it has something to do with how WP stores the value – perhaps its datatype related? Or maybe I should not be using $wpdb->get_var(). Any guidance will be greatly appreciated.

3 s
3

The meta_value is not of an integer type for max to return proper values. You can use mysql cast method to convert into integers as follows:

SELECT max(cast(meta_value as unsigned)) FROM wp_postmeta WHERE meta_key='price'

Leave a Comment