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® + Windows Live™.
http://www.microsoft.com/windows/shop/specialoffers.mspx?ocid=TXT_TAGLM_CPC_MediaCtr_bigscreen_102007
