List:General Discussion« Previous MessageNext Message »
From:Chris W Date:November 25 2007 7:41am
Subject:Re: Incrementing a "Private" Integer Space
View as plain text  

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

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