I have written a query that results in data being retrieved for the top 5 customers in my table per account manager.
Here is the query:
SELECT account_manager_id, mgap_ska_id, total
FROM
(SELECT account_manager_id, mgap_ska_id, mgap_growth + mgap_recovery AS total,
@grp_rank := IF(@current_accmanid = account_manager_id, @grp_rank + 1, 1) AS grp_rank,
@current_accmanid := account_manager_id
FROM mgap_orders
ORDER BY total DESC
) ranked WHERE grp_rank <= 5
and here is the result of the query:
account_manager_id mgap_ska_id total
159840 5062352 61569.21
159840 5062352 61569.21
159840 5062352 61569.21
159840 5062352 61569.21
159840 5062352 61569.21
160023 5024546 52244.29
160023 5024546 52244.29
160023 5024546 52244.29
160023 5024546 52244.29
160023 5024546 52244.29
159669 5323292 50126.38
159669 5323292 50126.38
159669 5323292 50126.38
159669 5323292 50126.38
159669 5323292 50126.38
As you can see the query is partially working as needed, except Im getting duplicates for mgap_ska_id whereas it should be five individual mgap_ska_id numbers.
and here is a sample of my data:
mgap_ska_id account_manager_id mgap_growth mgap_recovery
5057810 64154 0 1160.78
5178114 24456 0 5773.42
5292421 160338 0 5146.04
5414091 24408 0 104.14
5057810 64154 0 1160.78
Can anyone see where Ive gone wrong in my query and how/where I might correct the error so I get the 5 top individual customers (mgap_ska_id) instead of the duplicated top single customer?
