Re: Get the primary key of the last record added

Bill Biesty wrote:
>
> >Allen Kirby <akirby_at_att.com> wrote:
> >You must use a column that
> >increases in value with each insertion, then select max of that column.
> >Sequence numbers or sysdate usually work fine. This can be implemented
> >with triggers, default values or in the application itself.
>
> Hmm. This is not always possible, especially if your sequence
> numbers are generated to distribute your rows in a cluster
> instead of piling them at the end. Plus if you commit there's
> no guarantee that the max will be the record your process inserted,
> since another process might have also just inserted after yours.
>
> You need to select on an alternate key (set of columns that
> uniquely identifies the row) in your where clause to get the PK.
>
> How does a trigger or default value return the value of the PK
> to the application???

<snip>

Bill,
I think you may have misunderstood the question (or maybe I did!). The question was how to find out which row in a table was the last to be inserted. What I proposed is to create a new column (could be used only for this purpose if necessary) and populate this column with either an ever-increasing sequence number or sysdate at the time of insert, using a default column value, a trigger that populates the column before insert, or coding it into the sql itself. Since the value is generated at the time of insert, it doesn't matter when the commit is done or if it is done at all, since all we're concerned about is the relative order of the inserts. We're not trying to return anything at the time of insert.

The select would select the primary key columns (or whatever you want it to select) from the table where this new column has the maximum value. I think this is the only way to accomplish something like this. If I'm missing something, please correct me.

---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.