Re: Restart increment to 0 each year = re-invent the sequences mecanism ?

On Mon, Apr 26, 2004 at 09:38:41 -0400,
btober(at)computer(dot)org wrote:
> > You don't have to mess with sequences.
> > If there are two fields ID and year then the next number is:
> >
> > next_number := ( select ID from table_name where year =
> > year_from_current_date order by ID desc limit 1 ) + 1;
>
> Gee, I wonder why no one else thought of that... lets see, what is this
> thing I've heard about called, er, what was that word... oh yeah, I
> remember: "concurrency". Hmm, I wonder...

Yes, you need to do a lock table if you do things that way. But that
still is probably what he wants to do. Sequences can leave gaps, which
he probably doesn't want. If that is true then things need to be
serialized somehow. I doubt that these reports are being generated
at such a rate that he needs to worry about contention problems with
locking the table.