Re: remove gaps in a table
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Wed, 20 Dec 2017 22:02:20 -0500
Message-ID: <p1f87p$eb4$1_at_jstuckle.eternal-september.org>
>
> I get it, don't touch the order_number.
> Actually, I like the idea of filling gaps with void orders.
>
> 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 !
Date: Wed, 20 Dec 2017 22:02:20 -0500
Message-ID: <p1f87p$eb4$1_at_jstuckle.eternal-september.org>
On 12/20/2017 6:13 PM, mireero wrote:
> 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.
> Actually, I like the idea of filling gaps with void orders.
>
> 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 !
You don't fill gaps. You don't allow them in the first place. Once you've reserved an ID (however you do it), you enter the row. It may be a valid row or one marked void, but you enter it. Do not allow updates or deletes on the table.
If this is for accounting, the rows cannot be deleted or altered once they are entered into the database. So you restrict anyone from doing these operations.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Thu Dec 21 2017 - 04:02:20 CET