barracuda barracuda - 3 months ago 9
MySQL Question

Create a Rank Column based on the max values of a distinct column that belongs to a group set

I need to create a ranking column with a rank for each distinct part_id of that is associated to an order_id based on the MAX value of build_steps for that part_id. The rank should be restarted whenever a new order_id is iterated.

I have the following fiddle but it is not creating the rank properly.

http://sqlfiddle.com/#!9/63d47/29

Below is my query

SET @current_rank = 0;
SET @prevOrder = null;
SELECT part_id, MAX(build_steps) AS max_build_steps,
@current_rank:= CASE WHEN @prevOrder = order_id
THEN @current_rank:=@current_rank +1
ELSE @current_rank:=1 END rank,
@prevOrder:= order_id as 'order_id'
FROM orders,
(SELECT @current_rank:=0) r
GROUP BY order_id, part_id
ORDER BY order_id desc, max_build_steps desc;


Table:

CREATE TABLE orders
(`part_id` int, `build_steps` int, `order_id` int)
;

INSERT INTO orders
(`part_id`, `build_steps`, `order_id`)
VALUES
(234554, 1, 1234),
(234554, 2, 1234),
(234554, 3, 1234),
(234554, 4, 1234),
(234554, 5, 1234),
(234554, 6, 1234),
(234554, 7, 1234),
(234554, 8, 1234),
(234555, 1, 1234),
(234555, 2, 1234),
(234556, 1, 1234),
(234556, 2, 1234),
(234556, 3, 1234),
(234557, 1, 1234),
(234566, 1, 5678),
(234566, 2, 5678),
(234566, 3, 5678),
(234566, 4, 5678),
(234566, 5, 5678),
(234567, 1, 5678),
(234567, 2, 5678),
(234568, 1, 5678),
(234569, 1, 5678)
;


Expected Result:

part_id, max_build_steps, rank, order_id

234566 5 1 5678

234567 2 2 5678

234568 1 3 5678

234569 1 4 5678

234554 8 1 1234

234556 3 2 1234

234555 2 3 1234

234557 1 4 1234


Current Query Results:

part_id max_build_steps rank order_id

234566 5 1 5678

234567 2 2 5678

234568 1 3 5678

234569 1 4 5678

234554 8 1 1234

234556 3 3 1234

234555 2 2 1234

234557 1 4 1234

Answer

Your question is a good demonstration that using session variables for such tasks relying on the engines execution order is ustable. In your case it seems that MySQL executes your operations in the SELECT part before it orders the result set. A workaround is to use an ordered subquery:

select part_id,
  max_build_steps,
  case when order_id = @order_id 
    then @rank := @rank + 1
    else @rank := 1
  end as rank,
  @order_id := order_id as order_id
from (
  select o.part_id, o.order_id, max(build_steps) as max_build_steps
  from orders o
  group by o.part_id, o.order_id
  order by o.order_id, max_build_steps desc
  limit 1000000000
) sub 
cross join (select @order_id := null, @rank := 0) init_session_vars

That works at least on sqlfiddle with MySQL 5.6. However people already reported issues, that newer MySQL versions do not order subquery results. That's why i added a huge limit, which is a workaround for MariaDB (cann't say if it works for MySQL 5.7).

However - you better fetch the ordered result and calculate the rankings in a procedural language where the execution order is guaranteed.

Update

Here is a different approach using temporary tables and an AUTO_INCREMENT column:

create temporary table tmp1 (
  ai int auto_increment primary key,
  part_id int, 
  order_id int,
  max_build_steps int
) select o.part_id, o.order_id, max(build_steps) as max_build_steps
  from orders o
  group by o.part_id, o.order_id
  order by o.order_id, max_build_steps desc
;

create temporary table tmp2 (order_id int, min_ai int)
  select order_id, min(ai) as min_ai
  from tmp1
  group by order_id
;

select t1.part_id,
  t1.order_id,
  t1.max_build_steps,
  t1.ai - t2.min_ai + 1 as rank
from tmp1 t1
join tmp2 t2 using(order_id);

sqlfiddle