Member Avatar for mehnihma

Hi

I need to change number ordering in table:

This is original:

SELECT MAX(`number`)
            FROM `'._DB_PREFIX_.'order_invoice`

It selects max number from column.
Now I need to change Invoice number to start from 1 again, now it is on 110.

Should I create new table and start from one or what would be the best solution to do it?
Is it possible if I manualy change last number to 1 then that it only checks last number in a column but not max number?

Thanks

what's the structure of the table?

Member Avatar for mehnihma
+---------------------------------+------------------+------+-----+---------+----------------+
| Field                           | Type             | Null | Key | Default | Extra          |
+---------------------------------+------------------+------+-----+---------+----------------+
| id_order_invoice                | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| id_order                        | int(11)          | NO   | MUL | NULL    |                |
| number                          | int(11)          | NO   |     | NULL    |                |
| delivery_number                 | int(11)          | NO   |     | NULL    |                |
| delivery_date                   | datetime         | YES  |     | NULL    |                |
| total_discount_tax_excl         | decimal(17,2)    | NO   |     | 0.00    |                |
| total_discount_tax_incl         | decimal(17,2)    | NO   |     | 0.00    |                |
| total_paid_tax_excl             | decimal(17,2)    | NO   |     | 0.00    |                |
| total_paid_tax_incl             | decimal(17,2)    | NO   |     | 0.00    |                |
| total_products                  | decimal(17,2)    | NO   |     | 0.00    |                |
| total_products_wt               | decimal(17,2)    | NO   |     | 0.00    |                |
| total_shipping_tax_excl         | decimal(17,2)    | NO   |     | 0.00    |                |
| total_shipping_tax_incl         | decimal(17,2)    | NO   |     | 0.00    |                |
| shipping_tax_computation_method | int(10) unsigned | NO   |     | NULL    |                |
| total_wrapping_tax_excl         | decimal(17,2)    | NO   |     | 0.00    |                |
| total_wrapping_tax_incl         | decimal(17,2)    | NO   |     | 0.00    |                |
| note                            | text             | YES  |     | NULL    |                |
| date_add                        | datetime         | NO   |     | NULL    |   
Member Avatar for mehnihma

For Now I have this:

SELECT COUNT( number ) FROM ps_order_invoice WHERE YEAR( date_add ) =  YEAR(CURDATE());

But how to make it to start from 1 again? This is for to reset it every year?

Will this work?

SELECT COUNT( number ) FROM ps_order_invoice WHERE DATE(date_add) >=  "2013-04-1 00:00:00";

If I am right?

Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.