List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:November 25 2007 8:48pm
Subject:RE: Incrementing a "Private" Integer Space
View as plain text  
Dave is trying to establish an algorithm which would fit your requirement
I can see column q is auto-increment which makes sense as you retain the capability to
generate a unique row but jumping into the middle of a conversation without knowing the
prior discussionWhat is/was/will be the purpose of column p..?Can we denormalise a bit
and extrapolate the value of column p based on known value of column
q?Martin-______________________________________________Disclaimer and confidentiality
noteEverything in this e-mail and any attachments relates to the official business of
Sender. This transmission is of a confidential nature and Sender does not endorse
distribution to any party other than intended recipient. Sender does not necessarily
endorse content contained within this transmission.> > On 11/25/07, Chris W
<2wsxdr5@stripped> wrote:> >> > Stut wrote:> > >> > >
insert into test1 set p = 2, q = (select max(q) + 1 from test1 as tmp> > > where
p = 2)> > >> > > Probably not very efficient, but it works.> >
>> > > -Stut> > >> > Auto increment is much easier to do. If
your primary key is made up of> > two fields and one of them is Auto Increment,
then it will have the> > desired behavior, just do this experiment and see....>
>> > CREATE TABLE `t` (> > `p` int(10) unsigned NOT NULL default '0',>
> `q` int(10) unsigned NOT NULL auto_increment,> > PRIMARY KEY (`p`,`q`)>
> ) ;> >> > INSERT INTO `t` (`p`,`q`) VALUES> > (1,NULL),> >
(1,NULL),> > (1,NULL),> > (2,NULL),> > (2,NULL),> > (2,NULL),>
> (2,NULL),> > (2,NULL),> > (3,NULL),> > (3,NULL);> >> >
--> > Chris W> > KE5GIX> > > Hi Chris,> > OK, I will try that.
Thanks for the help.> > My assumption in reading your original post was that you
didn't understand> what I wanted to do (but you did).> > In order for me to use
the solution you have proposed, in addition to> working, this behavior would have to
be described in the MySQL manual. The> reason is that this seems to be the kind of
behavior that could change from> version to version.> > <BEGIN IMPORTANT
POINT>> I don't suppose you know the section in the manual that defines the
behavior> you're describing?> <END IMPORTANT POINT>> > The issue is
that unless this behavior is defined, changing autoincrement> from the behavior you
described to a simpler version that just uses> ascending integers with no other
context is the kind of thing where the guys> at MySQL might reason that it won't
affect anyone or wasn't defined in a> specific way anyway.> > Strictly speaking,
this feared change wouldn't affect the logical correct> operation of my database (there
would still be key uniqueness), but the neat> n, n+1, n+2 ordering I'm looking for in
"q" would confuse humans.> > Phrased more compactly: unless MySQL calls out this
behavior in the> documentation, your solution scares the snot out of me.> >
Thank you sincerely, Dave.
_________________________________________________________________
Put your friends on the big screen with Windows Vista® + Windows Live™.
http://www.microsoft.com/windows/shop/specialoffers.mspx?ocid=TXT_TAGLM_CPC_MediaCtr_bigscreen_102007
Thread
Incrementing a "Private" Integer SpaceDavid T. Ashley25 Nov
  • Re: Incrementing a "Private" Integer SpaceChris W25 Nov
  • Re: Incrementing a "Private" Integer SpaceStut25 Nov
    • Re: Incrementing a "Private" Integer SpaceDavid T. Ashley25 Nov
      • Re: Incrementing a "Private" Integer SpaceStut25 Nov
    • Re: Incrementing a "Private" Integer SpaceChris W25 Nov
      • Re: Incrementing a "Private" Integer SpaceDavid T. Ashley25 Nov
        • Re: Incrementing a "Private" Integer SpaceChris W25 Nov
        • RE: Incrementing a "Private" Integer SpaceMartin Gainty25 Nov
          • Re: Incrementing a "Private" Integer SpaceDavid T. Ashley25 Nov