Re: remove gaps in a table
Date: Thu, 21 Dec 2017 00:13:16 +0100
Message-ID: <5a3aee8c$0$11762$426a74cc_at_news.free.fr>
[Quoted] On 12/20/2017 11:17 PM, Geoff Muldoon wrote:
> 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.
>
> 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
>
I get it, don't touch the order_number.
[Quoted] Actually, I like the idea of filling gaps with void orders.
[Quoted] I am still wondering how one could find the first gap, using mysql only ? I mean, the lowest order_number that is non existing. Or I could say, the highest order_number before any gap + 1.
To be complete:
I created the gaps by manually deleting fake/testing rows in the table.
I don't have access to a dev/test server...
AFAIK, I'm the only "source" of gaps, so it should be fine in the future.
Thanks everyone, I appreciated your comments ! Received on Thu Dec 21 2017 - 00:13:16 CET