Re: Find most recent rows, generically?

in article 604bb90c.0202192008.53621721_at_posting.google.com, DL at donlcs_at_yahoo.com wrote on 2/19/02 8:08 PM:

> The question was really, how
> to determine if Oracle 'sequence' exists for a given column, say, PK
> column (EMPNO) for EMPLOYEE table, and if so, where to find the
> 'sequence' numbers for that column (EMPNO)? Thanks.

Assignment of the current sequence value to a column is done in application code (either in the application, or via a trigger or stored procedure in the DB). Note that sequences themselves are not 'bound' to a column in the way that 'identity' is bound to a column in SQLServer. Also not that sequences don't necessarily increment (they can be defined to decrement also).

So there is no data dictionary managed way to determine which column is actually using a given sequence, or vice versa.

And to go back to your original question (which John answered), you simply cannot count on a column containing values generated from a sequence to determine the most recent rows.

If you have this as a requirement in your data model, the easiest thing is to add a timestamp column (DATE or DATETIME) to your table. And it's portable across all database platforms !!

FYI - Even though it appears that you could, you simply cannot rely on a primary key to identify the most recent rows in ANY database (whether it's a true primary key, or a surrogate key based on an identity/assigned from a sequence). Even timestamp columns can be overloaded in the application with earlier values.

The only 'real' potential solution to the problem space of finding the most recent rows in a table is surprisingly only supported in Oracle9i - use flashback query to diff the current table with it's older version from your given reference point in time. And even then this diff would have to be keyed on the primary key to determine what had been changed. Received on Wed Feb 20 2002 - 06:04:14 CET