Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 10 down vote accepted

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'

share|improve this answer
    
Excellent, that worked perfectly. Thank You! –  Kirkland Jun 11 '12 at 6:44
    
Including only published posts: pastebin.com/pR6SDEdf –  npc May 15 '13 at 23:24

I modified szajmon solution to work with wp_cache and fix the sql syntax error I get.

wpdb->prepare wraps the $end variable with quotes and that trows an error (at least in my case)

function get_min_max_meta_value( $type = 'max', $key ){

    global $wpdb;
    $cash_key = md5($key . $type);
    $results = wp_cache_get($key);

    if($results === false){

        $sql = "SELECT " . $type . "( cast( meta_value as UNSIGNED ) ) FROM {$wpdb->postmeta} WHERE meta_key='%s'";
        $query = $wpdb->prepare( $sql, $key);

        return $wpdb->get_var( $query );

    }

    return $results;
}
share|improve this answer

I modified the original function and KDM's solution to have a more universal function. It goes like this:

function end_meta_value( $end = "max", $meta )
{
    global $wpdb;
    $query = $wpdb->prepare( 
        "SELECT %s( cast( meta_value as UNSIGNED ) ) FROM {$wpdb->postmeta} WHERE meta_key='%s'",
        $end,
        $meta
    );
    return $wpdb->get_var( $query );
}

This way you can get both minimum and maximum values of any custom meta_value. I also changed wp_postmeta to $wpdb->postmeta to fit any prefix you use.

Note: If you want query for a digit, replace %s in the $wpdb->prepare() statement with %d.

share|improve this answer
    
Thanks for your answer. I get an SQL syntax error. The problem is that wpdb->prepare wraps the $end variable with '' so the query is SELECT 'max'(cast.... and that throws an error. You can conjunct it like $sql = "SELECT " . $end . "(cast...". That worked for me. –  Laxmana May 6 at 14:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.