Re: remove gaps in a table

[Quoted] On 12/20/2017 02:13 AM, J�rgen Exner wrote:
> On Wed, 20 Dec 2017 12:02:36 +1100, Geoff Muldoon
> <geoff.muldoon_at_trap.gmail.com> wrote in comp.databases.mysql:
>

[Quoted] >> In article <3gaj3dd0dm04len4iejom72dg8otbrkdo5_at_4ax.com>,
>> jurgenex_at_hotmail.com says...
>>>
>>> On Wed, 20 Dec 2017 01:01:40 +0100, mireero <mireero_at_free.fr> wrote in
>>> comp.databases.mysql:
>>>
>>>> On 12/20/2017 12:29 AM, J?rgen Exner wrote:
>>>>> On Tue, 19 Dec 2017 22:03:16 +0100, mireero <mireero_at_free.fr> wrote in
>>>>> comp.databases.mysql:
>>>>>
>>>>> [...]
>>>>>> I'm looking for a way to "reduce" the order_number, to remove gaps, that
>>>>>> is to obtain:
>>>>> [...]
>>>>>> 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.
>>>>>
>>>>> Are you sure this is a good idea? I just imagine a customer calling:
>>>>>
>>>>> "Good morning. This is ACME Corporation customer support. How may I help
>>>>> you".
>>>>> "Good morning. We have an issue with our last order. The order number is
>>>>> 12345".
>>>>> "Just a second, let me look this up. Ah, yes, I see. What is the problem
>>>>> with the 50 anal beads?"
>>>>> "Anal beads? This is the Jesuit Convent of xyz and I am calling about
>>>>> the order for 30 rosaries!".
>>>>
>>>> Thanks for all the answers.
>>>>
>>>> I do get the idea, don't store the order_number in the database.
>>>
>>> Au contraire. Typically the order number is _THE_ unique identifier
>>> around which everything else revolves. So is must be stored in the DB.
>>
[Quoted] >> My reading of the original post is that there were TWO fields:
>> order_id - autoincrement primary key
>> order_number - per month no-gaps business counter, not globally unique

[Quoted] [Quoted] Actually, this order_number is the public identifier of the order. It is the number shown in the invoice.
I made a mistake, it rolls back to 1 every year, not every month but the idea is the same.
I don't know exactly why there shouldn't be any gaps but anyway that's the specification I got.
For some accounting reasons, I guess.
So, if I don't store it in DB, and some code somewhere tries to retrieve a row just knowing the order_number and the date, I guess I have to play with LIMIT.

Note: my new spec is to fill the gaps with some today's orders, and to [Quoted] avoid deleting orders from now on.
[Quoted] I'm not sure of the implications with the dates, though... (maybe none, as far as I do it before next year). Received on Wed Dec 20 2017 - 12:01:29 CET