To display top 4 rows using mysql Rank is displaying wrong

Question

I need to display the top4 and lease 4 rows based Amount and group by agentId but here rank is showing wrong And how to show least(last 4 rows?) schema:

 AgentID amount 
    1      3000
    1      3200
    2      9000
 SELECT Agentid,SUM(AmountRecevied) as Amount,@rownum := @rownum + 1 AS Rank
 FROM collection ,(SELECT @rownum := 0) r
 GROUP BY AgentID
 ORDER BY Amount DESC
 limit 4;

enter image description here

here based on amount if 800000 is the higher amount it's should display the rank 1 but it's showing 4 — user123, May 30 '14 at 05:29
My question is why do you need to calculate rank in query? Top most is 1, second is 2 and so on.. — Linga, May 30 '14 at 05:30

Raging Bull · Accepted Answer · 2014-05-30T07:02:21.823

2

Try this way:

SELECT T.Agentid,T.Amount, @rownum := @rownum - 1 AS Rank
 FROM
 (SELECT Agentid,SUM(AmountRecevied) as Amount
 FROM collection 
 GROUP BY AgentID
 ORDER BY Amount
 LIMIT 4) T,(SELECT @rownum := 11) r

edited May 30 '14 at 07:02

answered May 30 '14 at 05:29

Raging Bull

18,593
13
50
55

How to display last four rows? here it's working fine – user123 May 30 '14 at 05:30
Last four rows means? – Raging Bull May 30 '14 at 05:32
last least four amount like last four ranks if 10 ranks than should show 7,8,9,10 – user123 May 30 '14 at 05:33
@sandeep: edited my answer. Try it now. I think that is what you are after. If not, please let me know. – Raging Bull May 30 '14 at 05:36
1

@sandeep: Your understanding and explanation on assiging rank seems unclear. From your posted query, most of those on SO assume that highest amount record will have highest rank, i.e. 1. – Ravinder Reddy May 30 '14 at 05:37
@RagingBull: Your edited answer may give wrong results, if that many number of records does not exist. – Ravinder Reddy May 30 '14 at 05:39
rank are displaying like 9,8,7,6 – user123 May 30 '14 at 05:41
i added this order by Rank asc fine now – user123 May 30 '14 at 05:49
hi Raging Bull small doubt i don't want to display the rank for last four rows how to do that? – user123 May 30 '14 at 06:03
@sandeep: You mean, you don't want to show rank, but it should select other columns? – Raging Bull May 30 '14 at 06:10
@sandeep: Can you show me the query you are trying. I will give it a try. – Raging Bull May 30 '14 at 06:13
same query which you give to me i am tiring with that – user123 May 30 '14 at 06:14
@sandeep: last 4 rows means 10,9,8,7? – Raging Bull May 30 '14 at 06:18
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS Rank FROM (SELECT Agentid,SUM(AmountRecevied) as Amount FROM collection ' at line 1 – user123 May 30 '14 at 06:25
@sandeep. I think we can't select some all fields from one column and some fields from another column unless we use subquery and joins...which will be a bad idea (considering the performance). – Raging Bull May 30 '14 at 06:30
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/54762/discussion-between-sandeep-and-raging-bull). – user123 May 30 '14 at 06:35
When multiple agents have same amount then ranks differ with this solution. Check my answer. – Ravinder Reddy May 30 '14 at 07:28

score 1 · Answer 2 · answered May 30 '14 at 05:40

1

Try this :

SELECT
   C.*,
   @rownum := @rownum + 1 AS Rank
FROM (
   SELECT
     Agentid,
     SUM(AmountRecevied) as Amount
   FROM collection
   GROUP BY AgentID
   ORDER BY Amount DESC
   LIMIT 4
) AS C, (SELECT @rownum := 0) r

answered May 30 '14 at 05:40

Tanatos

1,857
1
13
12

score 1 · Answer 3 · answered May 30 '14 at 07:22

In case of amount matching for different agentids, then, I believe, ranks should be assigned same.

This solution should help you:

select 
  /*case when rank>6 then '' else rank end as */
  rank, agentid, amount
from (
  select agentid, @ca:=amount amount
    , case when @pa=@ca then @rn:=@rn
           else @rn:=( @rn + 1 )
      end as rank
      , @pa:=@ca as temp_currAmount
  from ( select agentid, sum(amount) as amount 
         from agents
         group by agentid 
         order by amount
        ) amounts_summary,
       (select @pa:=0, @c0:=0, 
               @rn:=0) row_nums
  order by rank desc 
) results 
where rank > 6
order by rank
;

Demo @ MySQL 5.6.6 Fiddle

And if you want no display ranks greater than '6' but empty, then
just uncomment the case line and comment the where condition line

select 
  case when rank>6 then '' else rank end as 
  rank, agentid, amount
from (
  select agentid, @ca:=amount amount
    , case when @pa=@ca then @rn:=@rn
           else @rn:=( @rn + 1 )
      end as rank
      , @pa:=@ca as temp_currAmount
  from ( select agentid, sum(amount) as amount 
         from agents
         group by agentid 
         order by amount
        ) amounts_summary,
       (select @pa:=0, @ca:=0, 
               @rn:=0) row_nums
  order by rank
) results 
-- where rank > 6
order by rank
;

You can modify asc or desc as required.

Demo @ MySQL 5.6.6 Fiddle

To display top 4 rows using mysql Rank is displaying wrong

3 Answers3

Linked