I am working with a data table that can have rows removed and added at any
point in time. I am following the SQLite Scrolling Cursor wiki
page<http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor>on
implementing paging. The only thing I have left to figure out is how
to
determine how many items are before the first item, so the correct page can
be determined. Here is an example based on the wiki page:
Assume the same Madonna example as the wiki page, here is how to get the
next 5 titles:
SELECT title FROM tracks
WHERE singer='Madonna'
AND title>:lasttitle
ORDER BY title
LIMIT 5;
I can determine the number of title before lasttitle with this:
SELECT COUNT(title) FROM tracks
WHERE singer='Madonna'
AND title<:lasttitle
I am having a problem doing this when scrolling backword. Again here is the
SQL from the Wiki to get the display set:
SELECT title FROM tracks
WHERE singer='Madonna'
AND title<:firsttitle
ORDER BY title DESC
LIMIT 5;
My thought is to find the minimum title from the display set, then use that
in the count select, so I started with this:
SELECT MIN(title) FROM tracks
WHERE singer='Madonna'
AND title<:firsttitle
ORDER BY title DESC
LIMIT 5;
But this gave me the VERY first title. It appears the MIN is executed
BEFORE the LIMIT. How do I go about getting the minimum title to use in one
compound select statement:
SELECT COUNT(title) FROM tracks
WHERE singer='Madonna'
AND title< [[Statement to get the minimum title]]
Sam
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
