CODEWITHSUNDEEP
mysqlsql
Stan
Stan

Reputation: 38255

MySQL to calculate ranking and update the original table

MySQL server 5.6.20 (latest version at the moment)

Given a price by date table. I added a new column "Rank", which represent the ranking to the item price by date.

Date      Item  Price  Rank
1/1/2014  A     5.01   0  
1/1/2014  B     31     0  
1/1/2014  C     1.5    0  
1/2/2014  A     5.11   0  
1/2/2014  B     20     0  
1/2/2014  C     5.5    0  
1/3/2014  A     30     0  
1/3/2014  B     11.01  0  
1/3/2014  C     22     0

How do I write a SQL statement to calculate the ranking and update the original table? Below is the expected table with ranking filled in. The ranking calculation is grouped by date (1/1, 1/2, 1/3, etc). 

Date      Item  Price  Rank
1/1/2014  A     5.01   2  
1/1/2014  B     31     1  
1/1/2014  C     1.5    3  
1/2/2014  A     5.11   3  
1/2/2014  B     20     1  
1/2/2014  C     5.5    2  
1/3/2014  A     30     1  
1/3/2014  B     11.01  3  
1/3/2014  C     22     2

Also, if the price is the same for several items, how would MySQL handle the ranking? For example:

Date      Item  Price  Rank
1/4/2014  A     31     0  
1/4/2014  B     31     0  
1/4/2014  C     1.5    0

Thanks.

Upvotes: 0

Views: 151

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can get the rank in a query using varibles:

  select t.*,
         (@rn := if(@d = date, @rn + 1,
                    if(@d := date, 1, 1)
                   )
         ) as rank
  from pricebydate t cross join
       (select @d := NULL, @rn := 0) vars
  order by date, price desc;

You can put this in an update using a join:

update pricebydate pbd join
       (select t.*,
             (@rn := if(@d = date, @rn + 1,
                        if(@d := date, 1, 1)
                       )
             ) as rank
        from pricebydate t cross join
             (select @d := NULL, @rn := 0) vars
        order by date, price desc
       ) r
       on pbd.date = r.date and pbd.item = item
    set pbd.rank = r.rank;

Upvotes: 3

David Lounsbrough
David Lounsbrough

Reputation: 430

I believe this will do exactly what you want:

Update YourTable As T1                                   
Set    ItemRank = (                                               
    Select ItemRank From (                                         
        Select Rank() Over (Partition By ItemDate Order By Price Desc) 
        As ItemRank, Item, ItemDate                                    
        From YourTable                                            
    ) As T2                                                        
    Where T2.Item = T1.Item                                        
    And   T2.ItemDate = T1.ItemDate                                
)

Duplicate Ranks would be handled as having equal ranks.

Upvotes: 1

Related Questions