Hi
Assume I have a simple table for a phone directory - names, addresses, and
phone numbers etc.
Then assume this is a vast table with many millions of records, and that
there are indices on name, phone number, etc.
Then assume I'm writing an app that displays the entire directory in a
window, ordered by name/number etc using a scrollbar to move up and down.
So I might have:
CREATE TABLE pb (id INT, name VARCHAR(40),...);
CREATE INDEX pb_idx_name ON pb(name);
..
Not a problem. I can use "SELECT * FROM pb ORDER BY name LIMIT x,y" etc as
my user pages up and down the list, or drags a scrollbar.
Next, assume the user wants to jump to a specific record in the list, or to
(say) the first entry for a specific name.
How do you do this with sqlite, without forcing the the app to fetch every
record and then do a manual comparison in the app?
E.g. my user wants the app's window to 'jump' to the first record with name
'John Smith'. I.e what my windowed app wants to do is to find the
'position' x of the first record in the name index that matches 'John
Smith', then use that x to both (i) set its vertical scrollbar position,
and (ii) to then issue something like "SELECT * FROM pb ORDER BY name LIMIT
x,20", whilst retaining the illusion that the window contains the whole
phone directory.
I do not want to do "SELECT * FROM pb WHERE name LIKE 'John Smith' ORDER BY
name" because that will just return the subset of matching records.
It seems to me that I need an interface to sqlite that says 'given a
specific index, and a tuple of values for the columns upon which that index
is constructed, how many records appear before and after the index position
given by that tuple'
Any ideas, or am I missing something obvious? Sorry if this is a dumb question.
Thanks
--
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.476 / Virus Database: 269.10.6/902 - Release Date: 15/07/2007 14:21
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
