List:General Discussion« Previous MessageNext Message »
From:David T. Ashley Date:November 25 2007 5:56pm
Subject:Re: Incrementing a "Private" Integer Space
View as plain text  
On 11/25/07, Stut <stuttle@stripped> 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.

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