MySQL "number" rows in result-CodePudding

This is my first question, so please bear with me. I have a stored procedure which selects orders and order line location ids. In the result, I need the ability to "sequence" each location in each order.

the query so far is:

   select DISTINCT oh.order_id, oline.ship_location_id, null
    from op_order_header as oh
    inner join op_order_line as oline ON
        oh.order_id = oline.order_id
    inner join co_products as pr ON
        oline.product_id = pr.product_id and
        pr.inventory_type = 'inventory'
    where oh.order_status = 'COMPLETE'

the result is:

 ---------- ------------------ ---------- 
| order_id | ship_location_id | sequence |
 ---------- ------------------ ---------- 
|    24737 |             6621 |     NULL |
|    24738 |             6623 |     NULL |
|    24743 |             6634 |     NULL |
|    24743 |             6635 |     NULL |
 ---------- ------------------ ----------

the required result is:

 ---------- ------------------ ---------- 
| order_id | ship_location_id | sequence |
 ---------- ------------------ ---------- 
|  1224737 |             6621 |         1|
|  1324738 |             6623 |         1|
|  1224743 |             6634 |         1|
|  1224743 |             6635 |         2|
 ---------- ------------------ ----------

As you can see from the above that the first 3 orders only have 1 location each - so this would be the only location (thus only 1 in this order's sequence), while the order 1224743 has 2 locations and thus 2 in the sequence.

Hope this makes sense and I am looking forward to your responses. thanks

CodePudding user response：

I will answer because the accepted answer is really hard to understand if you are novice. You can order the data in a subquery and make a sequence by counting: if the previous value (prev) of the order_id is the same, add one to sequence, if not, not add anything.

select order_id,ship_location_id,sequence
  from (
          select order_id,
                 ship_location_id,
                 @row:=if(@prev=order_id,@row,0)   1 as sequence,
                 @prev:=order_id
            from tbl
        order by order_id,ship_location_id 
)t

CodePudding user response：

If running MySQL version >= 8.0 you could use ROW_NUMBER() -

SELECT
    order_id,
    ship_location_id,
    ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY ship_location_id) AS sequence
FROM tbl;

MySQL ROW_NUMBER() docs