Re: Retrieving first occurrence - How?

add 2 columns( last_update,create_date) to your table then use triggers to set the appropriate column :

for instance:
( very pseudo code)
after update set last_update := to_char(sysdate,'MM-DD-YYY HH:MM:SS');

after insert set create_date :=to_char(sysdate,'MM-DD-YYY HH:MM:SS');

-
you won't need one for deletes since you will not select deleted records...

then a
 select record where create_date = max(last_update) or last_update = max(last_update);
will retrieve the most recently added or modified records...  

GF <GAYLEN.FRALEY_at_MAIL.SPRINT.COM> wrote:

>A datetime stamp is just as unreliable. The datetime stamp is updated
>when a row is updated. I would have to have a datetime stamp for a new
>record and then a datetime stamp for updates and then a datetimestamp
>for deletes, etc. to ad-nauseum. I understand and appreciate all of
>the feedback. These are paths that I have already been down and
>understand the pros and cons of all of them. I just wanted to be sure
>that I have gleaned the knowledge of the rest of you. There is always
>that wee small chance that I, being the greatest SQL programmer of all
>time, may have missed one small, seemingly insignificant detail. Wait
>a minute, what was I thinking!
>
>Thanks to all.
>
>In article <8ngke7$ic9$1_at_nnrp1.deja.com>,
> mungoh_at_my-deja.com wrote:
>> In article <399AC15E.2CB1926C_at_Unforgetable.com>,
>> Walter T Rejuney <BlueSax_at_Unforgetable.com> wrote:
>> > GF wrote:
>> > >

 I just always want the last record
>> > > that was added. There is no timestamp field.
>>
>> Bluntly, although you may find a mechanism which appears to do what
>> you want, it is (as others have said) unreliable.
>> Donning my flame-proof suit, how's about saying "I have no money but I
>> want to buy a car" instead of "there is no timestamp but I want the
>> last record added".
>>
>> >
>> > In a case like you describe probably the best way to provide that
 type
>> > of data is to have a non-wrapping sequence that has enough digits
 that
>> > it will never be exhausted
>>
>> Bascially, a re-vamp of a timestamp which shows sequence. This will
>> work, but I worry that using Oracle sequences you may care to consider
>> the effect of CACHE on a sequence. You can (of course) turn such
>> caching off, but at the end of the day extra effort to add such a
>> new column might be better spent in adding a DATE column and be
>> done with it.
>>
>> Hope this is of use,
>>
>> Mungo
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Thu Aug 17 2000 - 09:28:41 CDT