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
add comment

2 Answers

up vote 9 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
add comment

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
add comment

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.