This project has moved and is read-only. For the latest updates, please go here.

Seek to record with minimum value of integer column

Apr 3, 2013 at 10:57 AM

how do I construct a search key to seek to a record with minimum integer value of primary key column ( coltyp = JET_coltyp.Long)?
Apr 3, 2013 at 12:42 PM
I must profess my ignorance of SQL at this point. :)

The more efficient way is to:
Api.JetSetCurrentIndex( null ); // null means the primary index
Api.TryMoveFirst(); // move to the first record in the index

To actually answer your question, you could do:
Api.JetSetCurrentIndex( null ); // null means the primary index
Api.MakeKey( sesid, tableid, 0, MakeKey.NewKey ); // Makes the key of '0's.
Api.JetSeek( sesid, tableid, SeekGrbit.SeekGE ); // Finds the first record.

Does that make sense?

Apr 3, 2013 at 1:05 PM
Thanks 4 prompt reply :)

The solution you suggested works if the smallest key is known (e.g. '0').
Well in my case rows of that table are inserted and removed during use, so the smallest integer in the column in question is changing and not known in advance.
With SQL you can get that using a "SELECT MIN" statement in an efficient way and I was wondering how this could be achieved using managed Esent without looping through a potentially large table.

martin (yes, me too ;-) )
Apr 3, 2013 at 1:19 PM
What a great name! :)

Oh right, it's a signed integer, so my proposal won't work for negative numbers. :) You could use the minimum 32-bit negative value instead (int.MinValue) to the JetMakeKey() call. The important part is the SeekGrbit.SeekGE option. That finds the first value that's greater than or equal to the key you specified.

The above assume that you have an index over the relevant column. But because you specified 'primary key', then you'll have the primary index, so then it must be true.

Apr 3, 2013 at 1:30 PM
Jepp, that could work :) - I just didn't realised the 'GE' in seekGrbit.SeekGE ... I should read more precisely :(
And yes, there is an index on that column.
I'll give it a try and let you know how it works out.

Thanks for the hint,
cheers :D
Apr 4, 2013 at 6:42 AM
Works like a charm with decent performance :D

Thanks for ponting me in the right direction