CODEWITHSUNDEEP
mysqlwordpress
qebas
qebas

Reputation: 89

Create a rank column on MySQL query

I have a MySQL query where I want to add a column to rank the users, based on their balance. The query is:

SELECT (display_name) 'Author',
IFNULL(ROUND(SUM(balance.meta_value),2),2) 'Balance',
IFNULL(ROUND((((SUM(balance.meta_value+bet.meta_value)-                                                         
SUM(bet.meta_value))/SUM(bet.meta_value))*100),2),2) 'Yield %'
FROM wp_posts p
 JOIN wp_users u 
ON p.post_author = u.ID
 LEFT JOIN wp_postmeta bet 
ON p.ID = bet.post_id AND bet.meta_key = 'bet'
LEFT JOIN wp_postmeta balance 
ON p.ID = balance.post_id AND balance.meta_key = 'balance'
WHERE p.post_status = 'publish'
GROUP BY u.ID
ORDER BY Balance DESC

I tried adding SET @rownum := 0; and @rownum := @rownum + 1 AS rank but it doesn't work, the rank is not sorted by the highest balance.

Can someone help me. Thanks.

Upvotes: 0

Views: 880

Answers (1)

juergen d
juergen d

Reputation: 204746

select *, @rownum := @rownum + 1 from
(
SELECT (display_name) 'Author',
    IFNULL(ROUND(SUM(balance.meta_value),2),2) 'Balance',
    IFNULL(ROUND((((SUM(balance.meta_value+bet.meta_value)-SUM(bet.meta_value))/SUM(bet.meta_value))*100),2),2) 'Yield %'
FROM wp_posts p
JOIN wp_users u 
    ON p.post_author = u.ID
LEFT JOIN wp_postmeta bet 
    ON p.ID = bet.post_id AND bet.meta_key = 'bet'
LEFT JOIN wp_postmeta balance 
    ON p.ID = balance.post_id AND balance.meta_key = 'balance'
WHERE p.post_status = 'publish'
GROUP BY u.ID
ORDER BY Balance DESC
)x, (select @rownum := 0) r

Upvotes: 3

Related Questions