How to generate a sequence order number in mysql

Question

I have the following data:

+--------+--------+--------+--------+----------+
| IDTRX  | IDCUST | ITEM   | IDORDER| ORDERSEQ |
+--------+--------+--------+--------+----------+
|1       |  A     |  SHOES | C18001 |          |
|2       |  A     |  BAG   | C18001 |          |
|3       |  A     |  TV    | C18005 |          | 
|4       |  A     |  IPHONE| C18008 |          |
|5       |  B     |  BAG   | C18002 |          | 
|6       |  B     |  TV    | C18003 |          |
|7       |  C     |  IPHONE| C18006 |          |
+--------+--------+--------+--------+----------+

I want to know how many times CUSTOMER items order how to query to fill the order sequence (ORDERSEQ column) grouped by IDCUST and IDORDER?

so the display of query results like this :

+--------+--------+--------+--------+----------+
| IDTRX  | IDCUST | ITEM   | IDORDER| ORDERSEQ |
+--------+--------+--------+--------+----------+
|1       |  A     |  SHOES | C18001 | ORDER-1  |
|2       |  A     |  BAG   | C18001 | ORDER-1  |
|3       |  A     |  TV    | C18005 | ORDER-2  | 
|4       |  A     |  IPHONE| C18008 | ORDER-3  |
|5       |  B     |  BAG   | C18002 | ORDER-1  | 
|6       |  B     |  TV    | C18003 | ORDER-2  |
|7       |  C     |  IPHONE| C18006 | ORDER-1  |
+--------+--------+--------+--------+----------+

Gordon Linoff · Accepted Answer · 2018-03-10 02:44:30Z

1

One method uses correlated subqueries:

select t.*,
       (select count(distinct t2.idorder)
        from t t2
        where t2.idcust = t.idcust and t2.idtrx <= t.idtrx
       ) as orderseq
from t;

Note: This does not format the value as a string. I think an integer is more useful in any case (and formatting as a string is trivial).

In many cases, variables are a more efficient solution:

select t.*,
       (@rn := if(@oc = concat_ws(':', t.idcust, t.idorder), @rn,
                  if(@oc := concat_ws(':', t.idcust, t.idorder), 1, 1)
                    )
                 )
       ) as orderseq
from (select t.*
      from t
      order by t.idcust, t.idorder, t.idtrx
     ) t cross join
     (select @oc := '', @rn := 0) params;

EDIT:

You can update a column in the table using join:

update t join
       (select t.*,
               (select count(distinct t2.idorder)
                from t t2
                where t2.idcust = t.idcust and t2.idtrx <= t.idtrx
               ) as new_orderseq
        from t
       ) tt
       on t.idtrx = tt.idtrx
    set orderseq = new_orderseq;  -- or whatever string formatting you want

edited Mar 10, 2018 at 2:44

answered Mar 9, 2018 at 14:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Irfani Firdausy Over a year ago

thanks for the answer, i mean is how to update the column orderseq?

Irfani Firdausy · Accepted Answer · 2018-03-10 03:29:37Z

yesss, working well.

-- Table structure for t_orders

DROP TABLE IF EXISTS t_orders; CREATE TABLE t_orders ( IDTRX int(11) NOT NULL AUTO_INCREMENT, IDCUST varchar(10) DEFAULT NULL, ITEM varchar(100) DEFAULT NULL, IDORDER varchar(10) DEFAULT NULL, ORDERSEQ varchar(10) NOT NULL, PRIMARY KEY (IDTRX) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

-- Records of t_orders

BEGIN; INSERT INTO t_orders VALUES (1, 'A', 'SHOES', 'C18001', ''); INSERT INTO t_orders VALUES (2, 'A', 'BAG', 'C18001', ''); INSERT INTO t_orders VALUES (3, 'A', 'TV', 'C18005', ''); INSERT INTO t_orders VALUES (4, 'A', 'IPHONE', 'C18008', ''); INSERT INTO t_orders VALUES (5, 'B', 'BAG', 'C18002', ''); INSERT INTO t_orders VALUES (6, 'B', 'TV', 'C18003', ''); INSERT INTO t_orders VALUES (7, 'C', 'IPHONE', 'C18006', ''); COMMIT;

And this query solution for update ORDERSEQ column :

update t_orders a join (select t.*, (select CONCAT('ORDER-',count(distinct t2.IDORDER)) from t_orders t2 where t2.IDCUST = t.IDCUST and t2.IDTRX <= t.IDTRX ) as new_orderseq from t_orders t ) tt on a.IDTRX = tt.IDTRX set a.ORDERSEQ = new_orderseq;

thanks all

Collectives™ on Stack Overflow

How to generate a sequence order number in mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related