Listing the most popular 8 city (custom fields) as used in posts

Question

Hi there i am creating a theme for a classifieds website and want to list the top 8 popular city which mentioned as posts custom fields. That means the top 8 cities which have the most classifieds. it will looks like this

http://i50.tinypic.com/6f7fww.png

i saw some examples of sorting custom fields but couldn't manage to make it work properly.

Answer 1

Let MySQL do the job:

global $wpdb;

$metakey = 'YOUR_METAKEY_GOES_HERE';

$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT meta_value, COUNT(*) AS counter from {$wpdb->postmeta} WHERE meta_key = %s GROUP BY meta_value ORDER BY counter DESC", $metakey, ARRAY_N )
    );
);

$resuls is an array with the ordered results.