Re: Incrementing a "Private" Integer Space
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.> > POINT>> I don't suppose you know the section in the manual that defines the > behavior> you're describing?> > > 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
RE: Incrementing a "Private" Integer Space
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.> > > I don't suppose you know the section in the manual that defines the behavior> you're describing?> > > 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
Re: Incrementing a "Private" Integer Space
David T. Ashley wrote: 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. I don't suppose you know the section in the manual that defines the behavior you're describing? From '3.6.9. Using AUTO_INCREMENT' "For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiplecolumn index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups." I didn't know it only worked in MyISAM and BDB... I almost always use MyISAM anyway. However I don't use that feature anymore due to my change in thinking on primary keys. The only time I use a primary key that has more than one field is if the table is a "many to many relationship" table and in that case neither field is auto increment. -- Chris W KE5GIX "Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm"; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a "Private" Integer Space
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. I don't suppose you know the section in the manual that defines the behavior you're describing? 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.
Re: Incrementing a "Private" Integer Space
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 "Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm"; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a "Private" Integer Space
David T. Ashley wrote: On 11/25/07, Stut <[EMAIL PROTECTED]> wrote: David T. Ashley wrote: I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? 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 -- http://stut.net/ Thanks for the help, Stut. Is there any way to modify the query so that it will also work on the first insert where there are no records with the specified p? (I.e. imagine in the table below that I wanted to insert with p=25 ... and I'd want the query to insert 25,1.) Thanks, Dave. You could probably do something using the if function. Untested but should work... insert into test1 set p = 4, q = if((select count(1) from test1 as tmp1 where p = 4) > 0, (select max(q) + 1 from test1 as tmp where p = 4), 1) -Stut -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a "Private" Integer Space
On 11/25/07, Stut <[EMAIL PROTECTED]> wrote: > David T. Ashley wrote: > > I have a table with two integer fields (call them p and q). > > > > When I insert a record with a known p, I want to choose q to be one > larger > > than the largest q with that p. > > > > What is the best and most efficient way to do this? > > > > For example, let's say the table contains (p,q): > > > > 1,1 > > 1,2 > > 1,3 > > 2,1 > > 2,2 > > 2,3 > > 2.4 > > 2,5 > > 3,1 > > 3,2 > > > > If I insert a new record with p=2, I would want to choose q to be > 6. But if > > I insert a record with p=3, I would want to choose q to be 3. > > > > Is there any alternative to locking the table, querying for max q with > the > > desired p, then inserting? > > 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 > > -- > http://stut.net/ Thanks for the help, Stut. Is there any way to modify the query so that it will also work on the first insert where there are no records with the specified p? (I.e. imagine in the table below that I wanted to insert with p=25 ... and I'd want the query to insert 25,1.) Thanks, Dave.
Re: Incrementing a "Private" Integer Space
David T. Ashley wrote: I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? 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 -- http://stut.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incrementing a "Private" Integer Space
David T. Ashley wrote: I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? Yes CREATE TABLE `t` ( `p` int(10) unsigned NOT NULL default '0', `q` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`p`,`q`) ) ; -- Chris W KE5GIX "Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm"; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incrementing a "Private" Integer Space
I have a table with two integer fields (call them p and q). When I insert a record with a known p, I want to choose q to be one larger than the largest q with that p. What is the best and most efficient way to do this? For example, let's say the table contains (p,q): 1,1 1,2 1,3 2,1 2,2 2,3 2.4 2,5 3,1 3,2 If I insert a new record with p=2, I would want to choose q to be 6. But if I insert a record with p=3, I would want to choose q to be 3. Is there any alternative to locking the table, querying for max q with the desired p, then inserting? Thanks.
