CODEWITHSUNDEEP
mysqlsql
rbawaskar
rbawaskar

Reputation: 1045

Rank sql columns

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 ?

Upvotes: 0

Views: 150

Answers (4)

spaghetti85
spaghetti85

Reputation: 1

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.

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

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.

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

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

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

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

Upvotes: 2

Related Questions