Hi Martin, The easiest way to restore context in this conversation is to go to the MySQL home page (www.mysql.com), then go to "Community", then "Lists", then to look at the archives of the main MySQL mailing list (this one).
I believe at this point that Chris and Stut answered my question decisively. They both gave me single-query methods of achieving the behavior that I want. Thanks to all ... Dave. On 11/25/07, Martin Gainty <[EMAIL PROTECTED]> wrote: > > > 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 <[EMAIL PROTECTED]> > 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(R) + Windows Live™. > > http://www.microsoft.com/windows/shop/specialoffers.mspx?ocid=TXT_TAGLM_CPC_MediaCtr_bigscreen_102007
