>> Hi there,
>>
>> I have a table that contains several records per person per day, as:
>> name,datetime,blabla
>> joe,2003-10.31-08.00,test1
>> joe,2003-10.31-09.00,test2
>> joe,2003-10.31-10.00,test3
>> joe,2003-10.31-11.00,test4
>> joe,2003-10.31-12.00,test5
>> etc
>>
>> I want to retrieve the last 4 things joe has done, ordered by
> datetime (asc).
>>
>> So using FIRST 4 and use 'desc' order is not ok... :-(
>>
>> How can I do that (using FB1.03)?
>
> Well, Yves, I suppose you could do
>
> SELECT <something>
> FROM <table> T
> WHERE NOT EXISTS(
> SELECT * FROM <table> t1
> JOIN <table> t2 on t2.person = t1.person
> JOIN <table> t3 on t3.person = t2.person
> JOIN <table> t4 on t4.person = t3.person
> where t.person = t1.person
> and t1.timecolumn > t.timecolumn
> and t2.timecolumn > t1.timecolumn
> and t3.timecolumn > t2.timecolumn
> and t4.timecolumn > t3.timecolumn)
> ORDER BY <whatever>
>
> I think this should work OK if you have an index on the person column
> and preferably one on the timecolumn as well.

Thank you for this proposal.
Well, I really miss the LAST keyword...

Another possibility would be to cache the complete resultset (which is never
really large in my case) in an tow-dimensional array, and the write out
only the last 4 rows...

regards,
Yves

> HTH,
> Set
>
>
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/