Re: remove gaps in a table

[Quoted] In article <p1docr$elc$1_at_jstuckle.eternal-september.org>, jstucklex_at_attglobal.net says...
>
> On 12/19/2017 11:55 PM, Geoff Muldoon wrote:
> > 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.
> >
> > 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
> >
>
> Your "solution" will change the number should the rows change. Juse
> because is reduces the order_number to remove gaps does not mean it is
> the right answer - or even a good answer.

[Quoted] The problem is with the specification, not the solution.

Given the additional information, the "no gaps" requirement must be seriously challenged. As it now appears that the order_number is the identifier used to communicate with customers, any "re-use" of this identifier for an alternative order_id is A Bad Thing. If a record has somehow been deleted in the database it does not mean that the "old" customer has also purged all their data, they could contact the company quoting the order_number which in the database now reference a completely different customer. Serious problem.

GM Received on Wed Dec 20 2017 - 23:17:18 CET