I have the following mysql query which works as expected.

SELECT DISTINCT u.id,
CASE WHEN a.result IS NULL THEN 0 ELSE a.result END 'result',
RANK() OVER(ORDER BY result DESC) as 'rank'
FROM wp_users u, wp_usermeta um,
(SELECT pm.meta_value, COUNT(*) result
FROM wp_postmeta pm, wp_posts p
where p.id = pm.post_id
and p.post_status = 'publish'
and pm.meta_key = 'wpcf-userid'
and pm.post_id in (select pm1.post_id from wp_postmeta pm1 where pm1.meta_key = 'wpcf-badge-name' and pm1.meta_value = 'LD' and pm1.post_id = pm.post_id) 
and pm.meta_value IN (select um.user_id FROM wp_usermeta um WHERE um.meta_value = 'VIC' and um.meta_key = 'wpcf-address-state')
GROUP BY pm.meta_value) a
WHERE u.id = um.user_id and a.meta_value = u.id
group by u.id

the result of the above is

I'm wanting to add the "T" string before the rank for any ties like the following:

Any suggestions?

CodePudding user response：

We can subquery and then use COUNT() as an analytic function to detect ties in the rank:

WITH cte AS (
    SELECT u.id,
           CASE WHEN a.result IS NULL THEN 0 ELSE a.result END AS result,
           RANK() OVER(ORDER BY result DESC) AS `rank`
    FROM wp_users u, wp_usermeta um,
         (SELECT pm.meta_value, COUNT(*) result
          FROM wp_postmeta pm
          INNER JOIN wp_posts p ON p.id = pm.post_id
          WHERE p.post_status = 'publish' AND
                pm.meta_key = 'wpcf-userid' AND
                pm.post_id IN (SELECT pm1.post_id FROM wp_postmeta pm1
                               WHERE pm1.meta_key = 'wpcf-badge-name' AND
                                     pm1.meta_value = 'LD' AND
                                     pm1.post_id = pm.post_id) AND
                pm.meta_value IN (SELECT um.user_id FROM wp_usermeta um
                                  WHERE um.meta_value = 'VIC' AND
                                        um.meta_key = 'wpcf-address-state')
          GROUP BY pm.meta_value) a
    WHERE u.id = um.user_id AND a.meta_value = u.id
    GROUP BY u.id
)

SELECT id, result,
       CASE WHEN COUNT(*) OVER (PARTITION BY `rank`) = 1
            THEN `rank` ELSE CONCAT('T', `rank`) END AS `rank`
FROM cte t
ORDER BY t.`rank`;