Re: last inserted raw (identity)

On Sat, Aug 02, 2003 at 12:22:28AM -0500, nolan(at)celery(dot)tssi(dot)com wrote:
> > > In short, I think the answer to the original question is that there is no
> > > reliable way to find out what the last record inserted was.
> >
> > It returns the last record *you* entered. If you want the last record
> > entered by anyone (committed ofcourse), you'd use order by x desc limit 1.
>
> I agree that currval() would return the last record *I* inserted, but
> I don't understand how an order by clause would always return the last
> record that *ANYBODY* inserted. (And I thought that was the original
> question, but perhaps I am mis-remembering it at this point.)

Given he was using @@identity, I'm assuming he needed the id just inserted.
>
> If another user has a cache of 10 sequence values, there's no way
> for me to know that. Thus when the other user inserts a record using
> one of those cached sequence values, it could be either higher or
> lower than the sequence value in MY most recently inserted record.
> (I think it could be higher only if I also have my own cache of sequence
> values, assuming that the sequence never wraps.)

If you have a cache then the IDs won't match the insert order. You'd use a
timestamp if you want actual times to be involved. However, values will only
show up once the transactions they were in commit, so finding the the last
value inserted right now is not possible.

In any case, I beleive currval() is the answer to the original question.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato