Re: How to get the latest record from the database

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