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
>Solution :
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