Re: remove gaps in a table

[Quoted] In article <p1coa6$341$1_at_jstuckle.eternal-september.org>, jstucklex_at_attglobal.net says...
>
> On 12/19/2017 5:43 PM, Geoff Muldoon wrote:
> > In article <5a397e95$0$31613$426a74cc_at_news.free.fr>, mireero_at_free.fr
> > 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.
> >
> > 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
> >
>
> That's will be a problem if the date is changed to a new month or a row
> is deleted. Everything after that will have a new number.

Yes.

[Quoted] Isn't what you describe just a typical scenario that would otherwise still result in the unwanted "sequence gap"?

What I suggested will always at run time: ""reduce" the order_number, to remove gaps" as per the stated requirement.

GM Received on Wed Dec 20 2017 - 05:55:17 CET