CODEWITHSUNDEEP
mysql
MakoBuk
MakoBuk

Reputation: 464

Distinct order-number sequence for every customer

I have table of orders. Each customer (identified by the email field) has his own orders. I need to give a different sequence of order numbers for each customer. Here is example:

----------------------------
|      email      | number |
----------------------------
|  [email protected]   |    1   |
----------------------------
| [email protected] |    1   |
----------------------------
|  [email protected]   |    2   |
----------------------------
|  [email protected]   |    3   |
----------------------------
|  [email protected] |    1   |
----------------------------
| [email protected] |    2   |
----------------------------

Is possible to do that in a simple way with mysql?

Upvotes: 1

Views: 164

Answers (4)

Fabio Bonfante
Fabio Bonfante

Reputation: 5198

If you want update data in this table after an insert, first of all you need a primary key, a simple auto-increment column does the job. After that you can try to elaborate various script to fill the number column, but as you can see from other answer, they are not so "simple way".

I suggest to assign the order number in the insert statement, obtaining the order number with this "simpler" query.

select coalesce(max(`number`), 0)+1 
from orders 
where email='[email protected]'

If you want do everything in a single insert (better for performance and to avoid concurrency problems)

insert into orders (email, `number`, other_field)
select email, coalesce(max(`number`), 0) + 1 as number, 'note...' as other_field
from orders where email = '[email protected]';

To be more confident about not assign at the same customer two orders with the same number, I strongly suggest to add an unique constraint to the columns (email,number)

Upvotes: 1

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

You can add number using SELECT statement without adding any columns in table orders.

try this:

SELECT email,
        (CASE email
        WHEN @email
        THEN @rownumber := @rownumber + 1
        ELSE @rownumber := 1 AND @email:= email END) as number
FROM orders 
JOIN (SELECT @rownumber:=0, @email:='') AS t

Upvotes: 0

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

I think one more need column for this type of out put.

Example

+------+------+
|    i |    j |
+------+------+
|    1 |   11 |
|    1 |   12 |
|    1 |   13 |
|    2 |   21 |
|    2 |   22 |
|    2 |   23 |
|    3 |   31 |
|    3 |   32 |
|    3 |   33 |
|    4 |   14 |
+------+------+

You can get this result:

+------+------+------------+
|    i |    j | row_number |
+------+------+------------+
|    1 |   11 |          1 |
|    1 |   12 |          2 |
|    1 |   13 |          3 |
|    2 |   21 |          1 |
|    2 |   22 |          2 |
|    2 |   23 |          3 |
|    3 |   31 |          1 |
|    3 |   32 |          2 |
|    3 |   33 |          3 |
|    4 |   14 |          1 |
+------+------+------------+

By running this query, which doesn't need any variable defined:

SELECT a.i, a.j, count(*) as row_number FROM test a
JOIN test b ON a.i = b.i AND a.j >= b.j
GROUP BY a.i, a.j

Hope that helps!

Upvotes: 0

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

create a column order_number

SELECT @i:=1000;
UPDATE yourTable SET order_number = @i:=@i+1;

This will keep incrementing the column value in order_number column and will start right after 1000, you can change the value or even you can even use the primary key as the order number since it is unique all the time

Upvotes: 0

Related Questions