Re: How to get the latest record from the database

How about a view instead of a table and trigger? /Anders

Fabien Roy wrote:
> Hi Stefan,
> 
> As some others have shown you some solution, I will tell you the fastest 
> and scalable (IMHO) technique for retrieving the info that you need.
> Assuming that the rate of insert is low, this solution that involve an 
> auxiliary table an a trigger.
> What you need is a table with the user id and the id of the latest 
> record for that user.
> Using a trigger is the most efficient but the application could also 
> maintain that table at the expense of enclosing the insert in the main 
> table and the update of that auxiliary table enclosed in a transaction.
> 
> advantages: fast (fewer round trip to the database, select max(column) 
> will always involve sorting at the database engine and this could slow 
> down in the case of large data-set, the auxiliary table is fairly small 
> (one row per user).
> 
> This technique of using aggregate table maintained by triggers seems to 
> be the fastest solution specially for large data-sets.
> 
> Hope that helps.
> 
> Fabien
> On Jun 21, 2004, at 9:14 AM, Stefan Apelt wrote:
> 
>> Hi,
>>
>> sorry for the crosspost, I am not sure how many people are reading the
>> eof-list.  My problem is as follows: I have a long list of records for
>> user actions, simplified like this:
>>
>>   time      user             action
>>
>>   18:12    user foo         opened document
>>   18:12    user bar         pasted paragraph
>>   18:13    user foo         opened another document
>>
>> The  problem is that there are a lot of these entries in the database.
>> Now  I  want  to specify a user and get only the latest entry for that
>> user  (the one with the highest timestamp). The latest timestamp could
>> also  be  some  time  in the past so I cannot compare with the current
>> time.
>>
>> My  solution 'til now would be to fetch all records for the user, sort
>> them  by  time and return the first (or last) element in the resulting
>> array.  However,  since  there  can be literally thousands of records,
>> this is going to be slow.
>>
>> Does  anyone  have  a better idea to produce SQL for only fetching the
>> one record in question? I would be very grateful for that!
>>
>> -- 
>> Cheers,
>>  Stefan
>>
>>
>>
>>
>> _______________________________________________
>> EOF mailing list
>> [email protected]
>> http://www.omnigroup.com/mailman/listinfo/eof