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
```