Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    38

    Unanswered: How to find autoincrement value of groups?

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Apr 2004
    Posts
    38
    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.

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2004
    Posts
    38
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Analyzer
    Well, let me to explain it.
    thank you

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



    Quote Originally Posted by Analyzer
    First reason, i don't want to increase the cust no too much. I want to use all numbers.
    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



    Quote Originally Posted by Analyzer
    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.
    don't worry, if you export your data, rows that aren't there won't take up any space

    just trust me on this



    Quote Originally Posted by Analyzer
    The third reason, to control and handle fields are easier when they are tidy
    i'm glad you added the smiley

    now i know you were just kidding all along!!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2004
    Posts
    38
    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

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by guelphdad
    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.
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •