Re: Find most recent rows, generically?

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.

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

> Hi all,
>
> First of all, some clarification is in order:
> With MS SQL Server, 'identity' serves as an attribute to integer or
> other numberic data type, it IS NOT a data type by itself, and I never
> said 'identity' is a data type. Secondly, again, assume we don't know
> if there's a date column in the table. 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.
>
> DL
>
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> news:<EJgc8.5529$S01.352296_at_sccrnsc02>...

>> There are sequences in Oracle and you could store them as integers, numbers,
>> strings, but there is not a column type of identity.  That is you cannot
>> look at the column type and determine that it is a sequence.  Sequences are
>> nice because if I have a table with a bunch of child tables I can insert a
>> record in the parent and then use that value (mySequence.currentval ) for
>> the related keys in the child tables.
>> 
>> If you need to know what the most recent rows are I think a date column is a
>> much better use.  Then you could know when.  You might be interested in how
>> many rows are generated per day which a sequence won't tell you.
>> Jim
>> 
>> "DL" <donlcs_at_yahoo.com> wrote in message
>> news:604bb90c.0202172222.a375f10_at_posting.google.com...
>>> Thanks for the response, Jim, let me re-phrase my question, with MS
>>> SQL Server, when I add a column and define it as integer, identity,
>>> later I can find out if that column is of data type int etc., what's
>>> Oracle's equivalent of "Identity" if any? and if yes, which system
>>> table do I go to determine it?  Thanks.
>>> 
>>> DL
>>> 
>>> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message

>>>> You can't.  If it is important to know if it was recent then you need a
>>>> column to model that attribute.
>>>> Jim
>>>> "DL" <donlcs_at_yahoo.com> wrote in message
>>>> news:604bb90c.0202150930.49ef0fc5_at_posting.google.com...
>>>>> Hi,
>>>>> 
>>>>> First of all, I thank those who have been very helpful in the past.
>>>>> How to find most recent rows, say, 10, (for insert and update
>>>>> transactions), generically?
>>>>> 
>>>>> For insert:
>>>>> Well, one can find PK column of a given table, suppose, single column,
>>>>> then determine data type of that column, if it is of integer or
>>>>> similar type data,
>>>>> 1(a) how would one know that it uses "identity" attribute for it
>>>>> (thus, incremental)?
>>>>> 1(b) and if it does not, how to proceed?
>>>>> 2, what if it is of random alphnumeric?
>>>>> 3, what if it is of composite keys?
>>>>> 
>>>>> For update:
>>>>> ???
>>>>> 
>>>>> * Question category 1 is most important to me for now.
>>>>> 
>>>>> Thanks.
>>>>> 
>>>>> DL