Cfrgtkky

My data looks like this:

Table Name = sales_orders



Customer_id| Order_id| Item_Id

-------------------------------

1          | 1      | 10

1          | 1      | 24

1          | 1      | 37

1          | 2      | 11

1          | 2      | 15

1          | 3      | 28

2          | 4      | 37

4          | 6      | 10

2          | 7      | 10

However, I need it to look like this:

Customer_id| Order_id| Item_Id |Order_rank

------------------------------------------

1          | 1      | 10       |    1   

1          | 1      | 24       |    1

1          | 1      | 37       |    1

1          | 2      | 11       |    2

1          | 2      | 15       |    2

1          | 3      | 28       |    3

2          | 4      | 37       |    1

4          | 6      | 10       |    1

2          | 7      | 10       |    2

Customer_Id is a unique person

Order_id is a unique order

item_id is the product code

To further explain, the first three lines are from Customer #1's first order (order_id = 1) where this person ordered 3 different items (10,24, and 37). They then purchased another order (order_id =2) with two other products. Person with customer_id =2 has 2 unique orders (4 and 6), while customer with ID '4' has one unique order (order_id =6)

Essentially, what I need to do is rank these orders by customer_id and order Id, so that I can say "Order_id = 7 is the second order for customer_id = 2, because Order_rank = 2"

The challenge here is that I can't use session variables (e.g. @grp := customer_id ) in the MySQL query

For example, a query such as this is NOT allowed:

SELECT 

customer_id,

order_id,

@ss := CASE WHEN @grp = customer_id THEN @ss + 1 ELSE 1 END AS 

order_rank,

@grp := customer_id 

FROM 

(

SELECT 

customer_id,

order_id

FROM sales_orders

GROUP BY customer_id, order_id

ORDER BY customer_id, order_id ASC

) AS t_1

CROSS JOIN (SELECT @ss := 0, @grp = NULL)ss



ORDER BY customer_id asc

Thanks for the help!

In a Correlated Subquery, we can Count(..) the unique and previous order_id values for a specific row's customer_id and order_id to determine the rank.

We need to count unique values because you have multiple rows per order (due to multiple items).

Query

SELECT 

  t1.Customer_id, 

  t1.Order_id, 

  t1.Item_Id, 

  (SELECT COUNT(DISTINCT t2.Order_id) 

   FROM sales_orders t2 

   WHERE t2.Customer_id = t1.Customer_id AND 

         t2.Order_id <= t1.Order_id

  ) AS Order_rank 

FROM sales_orders AS t1;

Result

| Customer_id | Order_id | Item_Id | Order_rank |

| ----------- | -------- | ------- | ---------- |

| 1           | 1        | 10      | 1          |

| 1           | 1        | 24      | 1          |

| 1           | 1        | 37      | 1          |

| 1           | 2        | 11      | 2          |

| 1           | 2        | 15      | 2          |

| 1           | 3        | 28      | 3          |

| 2           | 4        | 37      | 1          |

| 4           | 6        | 10      | 1          |

| 2           | 7        | 10      | 2          |

View on DB Fiddle

You can use a correlated subquery:

select so.*,

       (select count(*)

        from sales_orders so2

        where so2.Customer_id = so.Customer_id and

              so2.order_id <= so.order_id

       ) as rank_order

from sales_orders so;

Or in MySQL 8+:

select so.*,

       dense_rank() over (partition by Customer_Id order by Order_Id) as rank_order

from sales_orders so;