Re: remove gaps in a table

[Quoted] 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.

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.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================