> 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.
Yes, that's true. You may want to try this instead:
SELECT MIN(title) FROM
(SELECT title FROM tracks
WHERE singer='Madonna'
AND title<:firsttitle
ORDER BY title DESC
LIMIT 5);
Pavel
On Sun, Oct 25, 2009 at 11:04 PM, Sam Carleton
<[email protected]> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
