Re: remove gaps in a table

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Wed, 20 Dec 2017 09:43:29 +1100
Message-ID: <MPG.34a4411c4b6564689896e2_at_news.albasani.net>


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

Original text of this message