[erlang-questions] Mnesia Top 1?

[Jayson Vantuyl]

I think QLC will do this.  Something like:

TH = mnesia:table(Tbl),
Q = qlc:q([X<-TH, X#tbl.objId = ObjId, X#tbl.time < FromTime]),
QS = qlc:keysort(#tbl.time,Q,[{order,descending}]),
QC = qlc:cursor(QS),
Row = case qlc:next_answers(QC,1) of
   [ Top1 ] -> Top1;
   [] -> undefined;
   Error -> Error
end.

Basically, query for those records, apply the descending sort on Time,  
get a cursor, and grab the first answer.  Use a similar process to get  
the upper bound as well.

On Sep 3, 2009, at 5:07 PM, Allan Merolla wrote:

> Hi,
> I have a massive table containing  time-series (values versus time)  
> for different object. Table structure is like ObjectID, Time, Value.  
> {ObjectID, Time} is the key.
> I have a function that returns interpolated values (e.g. a value for  
> each 10min) for a time range and object ID called getList(ObjId,  
> FromTime, ToTime).
>
> I use the following qlc query to get un-interpolated data.
> qlc:q([X<-mnesia:table(Tbl), X#tbl.objId = ObjId andalso  
> X#tbl.time>=FromTime andalso X#tbl.time<ToTime ]). Things work fine  
> so far but the problem is that I need to know what had been the  
> value for ObjId for a record just before FromTime and also a record  
> just after ToTime.
> I am new to mnesia, but in SQL the answer is:
> SELECT TOP1 * FROM Tbl WHERE [ObjectID]=ObjID AND [Time]<FromTime  
> ORDER BY [Time] DESC.
>
> I know in sql if I put an index on the [Time] column, the above  
> operation would be fast even in a massive table. The question is:  
> What is the most efficient way to do the above top1 query in mnesia?  
> I found that there is a function called prev(key) in mnesia that  
> returns the key for the previous record but it won't help me since  
> previous record may belong to another Object.
>
> Best Regards//
>
>
>
>
> Allan Merolla
> Analyst Programmer
>
> One Team, One Dream, Zero!
> Incidents & Non Conformances
> PACE ITS
> Level 2, 120 Wickham Street
> Fortitude Valley QLD 4006
> PO Box 141, Albion QLD 4010
> Phone:  +61 7 3257 4711
> Fax:      +61 7 3257 4788
> Mobile:  0430 300 277
> allan.merolla@REDACTED<mailto:caleb.rigby@REDACTED>
> http://www.paceits.com<http://www.paceits.com/>
> Notice: if you are not an authorised recipient of this e-mail,  
> please contact the sender by return e-mail. In this case, you should  
> not read, print, re-transmit, store, act or rely on this e-mail or  
> any attachments, and should destroy all copies of them. This e-mail  
> and attachments are confidential and may contain copyright material  
> of the sender or other parties. You should only re-transmit,  
> distribute or commercialise the material if you are authorised to do  
> so. This notice should not be removed.
>
>



-- 
Jayson Vantuyl
kagato@REDACTED