Dear All,

I need to solve an autoincrement problem in my database.I have 2 group
numbers and sort numbers are attached to them.For example :

100-1
100-2
100-3
100-4
100-5
.
.
101-1
101-2
101-3
101-4

So i need two different group but i need to assign the latest number to
new record of the group.For example when i want to get the latest numer
of group 100, it should give 6.But for 101 it should give 5 (I'm mentioning
about the example above).But if i delete a record in it, it should give me
the deleted records number then latest number.For example :

100-1
100-2
100-3
100-4
*** Deleted ***
100-6
100-7
*** Deleted ***
100-9

When i request the latest number it should give me 5,8,10,11,12,13,14....
So how to achieve this goal? I can not find an easy way to do this.Could you give me an example please? I'm using Zeos 6.1+BCB+MySQL 5.0

Analyzer

What are your group numbers? What is the DDL for the table you are using (this is going to be really tough to explain without column names) ? What have you tried so far, and how did that work out?

Based on what you've described so far, this seems trivially easy. I must be missing something, and I'm trying to pin it down so I don't lead you down the wrong track.

-PatP

Hi Pat,

It is not so easy for me Maybe you understood it totally correct. Let me to explain it in details.
I have 2 different colums in my table.One of them called "group no" the other is "cust no".I want to assign new numbers to customers in my table.But there is a problem.Look at this please :

Group No Cust No
--------- --------
100 1
100 2
101 1
100 3
100 4
100 5
101 2
101 3
101 4

As you see, i have 2 different groups and the group 100 has 5 customers and 101 has 4 customers.When i want to add a new customer to 100, it should give me the number 6.But if i want to add to 101 it should give 5.
To this point, i can handle it.I can store the latest number and increase it +1.
But if i delete a record from groups, i want my procedure to give deleted records numbers first, then new numbers.Like this : ( For example the group 100) :

100 1
100 2
100 3
100 5
100 6
100 8
100 9

As you see in this group 100-4 and 100-7 are missing.So when i request a new number it should give me 100-4, 100-7, 100-10, 100-11, 100-12, 100-13 ... goes like this.
So how can i do that?

Analyzer.

I want to ask a question here, I don't want to sound condescending or anything. I can't honestly figure this out, it is asked over and over again on many of the database discussion boards I visit.

Why is it necessary to retrieve 'missing' auto increment numbers? In this case for customers.

I'm just not sure why it matters whether or not a number is skipped over. Is it generally something in people's coding that should be done differently, so that 'missing' numbers don't matter?

Analyzer, I'm not specifically thinking of you, if Pat or Rudy or someone else can shed light on this, as well as Analyzer specifying in your case, it might help me understand thought process behind this so if I'm helping in the future I know why people might want this and be able to provide reasonable answer why it is unneccessary.

Thanks,
Dave

everything would be fine as long as you do not need to "fill the gaps"

to re-use a number in this situation could be very bad

very, very, very bad

if you can just leave the gaps for the ones deleted, then AUTO_INCREMENT can generate the new custno within each groupno

NOTE: it will re-use the highest number in the group if the highest number is deleted, so use with extreme caution

see the mysql docs for the example
http://dev.mysql.com/doc/mysql/en/ex...INCREMENT.html

Right you are Rudy, and Analyzer had asked to fill the gaps if you look at the last para. of his question. My question, is why do people want to do this? Why does there seem to be a need for folks to want to fill in the gaps? I'm just trying to understand something that I would think would go against the grain so to speak. If I understand the WHY I figure I could aleviate their fears and explain why their line of thinking is troublesome.

Again, not meant to discourage anyone, but looking at a different line of thinking than my own.

i don't know why, myself, i can only guess that it might be related to "neatness"

filling in the gaps is actually counter-intuitive to me

hard to put myself into the other person's mindset

i've written about this myself: Gaps in autosequence numbers (site regisrtation may be required, but it's free)

Well, let me to explain it.I'm trying to build a table+program to save customer's data.So, a lot of people will use the same table and some of them will enter incorrect data and they will delete some records.First reason, i don't want to increase the cust no too much.I want to use all numbers.The second reason, i will export all data to another platforms, another programs such as M$ Excel.So i don't want to leave blank fields when exporting.The third reason, to control and handle fields are easier when they are tidy

That's why i want to fill all gaps.I dont know the other's aim

Analyzer

thank you

but as i said in my article, give me your reasons and i'll try to talk you out of it



let's assume you use INTEGER for the customer number

if you add new customers at the rate of 100 per second, you will run out of numbers in approximately 68 years

if that's not enough for you, use BIGINT, in which case -- remember, at 100 PER SECOND -- you would run out of numbers in approximately 292 billion years, which some people think is longer than the expected life of the known universe



don't worry, if you export your data, rows that aren't there won't take up any space

just trust me on this



i'm glad you added the smiley

now i know you were just kidding all along!!!!

Well, i'm sorry i'm not kidding i really want to achieve this.I worked on it and i almost found a solution on it.If you are not interested on it, please just leave it. Maybe some other person want to do this too.I can give my clues who are interested on this.

Thanks in advance.

Analyzer

This is actually a very bad practice. Once a number is used, from a relational/logical/practical standpoint it should never be reused in that table, for anything, ever! This is a reciepe for disaster in the long run.

There are a number of folks that think of "sequence numbers" that need to be contiguous, and have no gaps. While those sequences are quite pretty, they are a logical "tar pit" that will swallow the unwary.

They really aren't hard to generate at all, but the consequences of generating them can cause havok for the unsuspecting! I've had to bail people out of the logical pits that these things cause...

If this was for a class where the instructor was going to demonstrate how badly then can cripple your application, I'd be Ok with it. It appears that Analyzer seriously wants to use them, and that is another thing altogether in my book!

-PatP