SELECT DISTINCT( comments.cid )                                 AS cid,
               comments.subject                                 AS
               comments_subject,
               comments.nid                                     AS comments_nid,
               comments.COMMENT                                 AS
               comments_comment,
               comments.FORMAT                                  AS
               comments_format,
               users_comments_profile_values_profile_nome.VALUE AS
               users_comments_profile_values_profile_nome_value,
               users_comments_profile_values_profile_nome.uid   AS
               users_comments_profile_values_profile_nome_uid,
               comments.timestamp                               AS
               comments_timestamp,
               comments.status                                  AS
               comments_status,
               flag_counts_comments.COUNT                       AS
               flag_counts_comments_count,
               flag_content.content_id                          AS
               flag_content_content_id,
               comments.uid                                     AS comments_uid
FROM   comments comments
       INNER JOIN flag_counts flag_counts_comments
         ON comments.cid = flag_counts_comments.content_id
            AND ( flag_counts_comments.fid = 3
                  AND flag_counts_comments.COUNT > 0 )
       LEFT JOIN flag_content flag_content_comments
         ON comments.cid = flag_content_comments.content_id
            AND flag_content_comments.fid = 3
       LEFT JOIN flag_note flag_note_comments
         ON comments.cid = flag_note_comments.content_id
            AND flag_note_comments.fid = 3
       INNER JOIN users users_comments
         ON comments.uid = users_comments.uid
       LEFT JOIN profile_values users_comments_profile_values_profile_nome
         ON users_comments.uid = users_comments_profile_values_profile_nome.uid
            AND users_comments_profile_values_profile_nome.fid = '3'
       LEFT JOIN flag_content flag_content
         ON comments.cid = flag_content.content_id
            AND ( flag_content.fid = 3
                  AND flag_content.uid = *** current_user *** )
GROUP  BY cid
ORDER  BY flag_counts_comments_count DESC