You have a mistake in your query
$sql = "SELECT
{$wpdb->prefix}commentmeta.comment_id, avg({$wpdb->prefix}commentmeta.meta_value) avg_meta_value
FROM
{$wpdb->prefix}commentmeta
LEFT JOIN
{$wpdb->prefix}comments
ON
{$wpdb->prefix}commentmeta.comment_id = {$wpdb->prefix}comments.comment_ID
WHERE
{$wpdb->prefix}commentmeta.meta_key = 'rating'
GROUP BY
{$wpdb->prefix}commentmeta.comment_id
ORDER BY
avg_meta_value desc";
$results = $wpdb->get_results($sql);
foreach ($results as $result)
{
echo $result->comment_id.'<br>';
}