Re: remove gaps in a table
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Tue, 19 Dec 2017 23:18:16 -0500
Message-ID: <p1coa6$341$1_at_jstuckle.eternal-september.org>
>
> that
>
>
> 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
>
Date: Tue, 19 Dec 2017 23:18:16 -0500
Message-ID: <p1coa6$341$1_at_jstuckle.eternal-september.org>
[Quoted] 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.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Wed Dec 20 2017 - 05:18:16 CET