Seek to record with minimum value of integer column

Apr 3, 2013 at 9:57 AM
Hi,

how do I construct a search key to seek to a record with minimum integer value of primary key column ( coltyp = JET_coltyp.Long)?
SQL aequivalent would be (SELECT MIN (MY_PRIMARYKEY) FROM MY_TABLE WHERE OTHER_COL = 'OTHER_VALUE'
Thanks
Developer
Apr 3, 2013 at 11:42 AM
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?

-martin
Apr 3, 2013 at 12: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 ;-) )
Developer
Apr 3, 2013 at 12: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.

-martin
Apr 3, 2013 at 12: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
martin
Apr 4, 2013 at 5:42 AM
Works like a charm with decent performance :D

Thanks for ponting me in the right direction
martin