Rank sql columns

Question

I have table like this:

+--------+--------+--------+
| color  | fruit  | amount |
+--------+--------+--------+
| red    | cherry |    124 |
| red    | plum   |     23 |
| green  | gauva  |    119 |
| green  | pear   |     14 |
| orange | orange |     23 |
+--------+--------+--------+

I want it to add rank like this.

 +------+--------+--------+--------+
 | rank | color  | fruit  | amount |
 +------+--------+--------+--------+
 |    1 | red    | cherry |    124 |
 |    2 | red    | plum   |     23 |
 |    1 | green  | gauva  |    119 |
 |    2 | green  | pear   |     14 |
 |    1 | orange | orange |     23 |
 +------+--------+--------+--------+

I need to rank it based on amount for each color (seperately). Is this possible ?

possible duplicate of [Mysql rank function](http://stackoverflow.com/questions/3333665/mysql-rank-function) — Mureinik, Jan 17 '14 at 09:14
@Mureinik i need to rank based on two columns, rather than one column. — rbawaskar, Jan 17 '14 at 09:17
possible duplicate of [Multiple Ranks in one table](http://stackoverflow.com/questions/3162389/multiple-ranks-in-one-table) — codeling, Jan 17 '14 at 10:51

score 2 · Accepted Answer · answered Jan 17 '14 at 09:21

select
color, fruit, amount,
case when (if(@prev_color != color, @rank:=1, @rank:=@rank + 1)) is null then null
     when (@prev_color := color) is null then null
else @rank end as rank
from your_table
, (select @rank:=0, @prev_color := null) v
order by color, amount desc

Ravinder Reddy · Answer 2 · 2014-01-17T10:55:38.437

If the ranks are given based on the high amount of color availability, then you can also find number of rows less or more than with the amount value. That number of rows would be the rank for the concerned color.

select
 ( select count( color ) from fruits_table 
   where color = f.color and amount >= f.amount
 ) as 'rank',
 color, fruit, amount 
from fruits_table f
order by color, amount desc
-- order by field( color, 'red', 'green', 'orange' ), amount desc
;

You can alter the order of fields by color to show on top or bottom as desired using the field function. See the commented order by clause above.

score 0 · Answer 3 · answered Jan 17 '14 at 09:36

Try this

SELECT  @row_num := IF(@prev_value=o.Customer,@row_num+1,1) AS RowNumber
       ,colour
       ,fruit
       ,amount
       ,@prev_value := colour
  FROM Table1,
      (SELECT @row_num := 1) x,
      (SELECT @prev_value := '') y
  ORDER BY colour,amount DESC

score 0 · Answer 4 · edited Feb 25 '17 at 04:52

Try using a row number function where you specify where to start then numbering over (partition by) and how to organize the rows within that partition (order by.)

 | rank | color  | fruit  | amount |
 +------+--------+--------+--------+
 |    1 | red    | cherry |    124 |
 |    2 | red    | plum   |     23 |
 |    1 | green  | gauva  |    119 |
 |    2 | green  | pear   |     14 |
 |    1 | orange | orange |     23 |
 +------+--------+--------+--------+

select distinct 
  row_number() over (partition by color order by amount desc) rank,
  color,
  fruit,
  amount
from
  foo
order by
  color, amount desc

You will find that the row numbering starts over with each new color, and that within each color, the rows will be ordered by the amount sorted in descending order.

The question is tagged MySQL. However, this answer doesn't seem to be in MySQL. — Pang, Feb 25 '17 at 04:56

Rank sql columns

4 Answers4