Re: remove gaps in a table

In article <5a397e95$0$31613$426a74cc_at_news.free.fr>, mireero_at_free.fr [Quoted] says...
>
> Hi,
>
> I have a table "orders" which is basically:
>
> create database tmp;
> use tmp
> create table orders (
> order_id int auto_increment primary key,
> order_number int,
> order_on date
> );

> I'm looking for a way to "reduce" the order_number, to remove gaps,
that
> is to obtain:

> How would you achieve this ?
> Is there a "mysql only" way to achieve the desired result ?
> I guess we could just iterate on all rows and set the order_number,
> beginning from 1 but I have the feeling I'm missing something.
>
> Note: in the real table, there are thousands of rows.

[Quoted] If you need order_number to be strictly sequential within a month with no gaps, the best solution is to not store it in the database at all.

Just use a ranking of the order_id, partitioned by month, and generate it on-the-fly each time the query is run.

GM Received on Tue Dec 19 2017 - 23:43:29 CET