JP wrote:
Jay Sprenkle wrote:
My application is geared towards users who want to find a specific
name
in a list of names, and then want to have the possibility to scroll
backwards or forwards. For example, if I search for "Sprenkle" I want
to show the user a window with "Sprenkle" in the middle, preceded
by the
50 names before it, and followed by the 50 names after it, and also to
be able to smoothly scroll in either direction.
I know the index contains sufficient data to do this, but there
seems to
be no way to use it from SQLite.
Get it in two chunks,
the first 100 names after the name in question:
select x from mytable where Name > 'sprenkle' limit 100
and the 100 names before the name in question:
select x from mytable where Name < 'sprenkle' limit 100 order by x desc
Right, that is the way I ended up doing it. I used "<=" instead of
"<" and added an "order by" to the first one ("order is never
guaranteed unless specifically declared").
Using a UNION of those two SELECTs does not work in 3.3.4 (bug?).
Executing them separately does work.
Thanks,
jp.
Technically the SQL standard doesn't allow an order by clause on a
subselect, however this is an extension that SQLite, and possibly other
database engines, allow. To do what you want in standard SQL you would
need two queries, the first two locate the beginning of your set of set
rows, and a second to get the rows you want.
select Name from mytable where Name < 'Sprenkle' order by Name desc
limit 1 offset 50;
You would save the result of this query and pass it in as the parameter
to the next query.
select * from mytable where Name >= ? order by Name limit 101;
In SQLite these can be combined into one query that gets the desired rows.
select * from mytable where Name >= (
select Name from mytable where Name < 'Sprenkle' order by Name
desc limit 1 offset 50)
order by Name limit 101;
This query works as expected in SQLite so it should be a work around for
your union bug.
HTH
Dennis Cote
P.S. And now 'Sprenkle' has been raised to the same heights as 'foo' and
'bar'.
