Sqlite Scrolling Cursor How to Scroll Correctly with duplicate names

I have implemented a scrolling cursor with SQL Lite and C#. Well the I am only testing with SQL in an SQL Lite tool.

So I have it functioning the scrolling cursor from:

http://sqlite.org/cvstrac/wiki?p=ScrollingCursor

So I have this working but the results are not correct.

SELECT title FROM tracks
     WHERE singer='Madonna'
       AND title>:lasttitle
     ORDER BY title
     LIMIT 5;

So say these are the fields in my track table:

track_id singer title media_format

and say I have a bunch of records ()

4    Madonna    <All my love demo>    .mp3
2    Madonna    <Be cool demo>    .wav
7    Madonna    <Like a Virgin>    .aaf
1    Madonna    <Like a Virgin>    .adi
5    Madonna    <Like a Virgin>    .mp4
11    Madonna    <Like a Virgin>    .wmv
3    Madonna    <Like a Virgin>    .mp5
8    Madonna    <Like a Virgin>    .mp6
10   Madonna    <Like a Virgin>    .mp7
9    Madonna    <Like a Virgin>    .mp8
6   Madonna    <Like a Virgin>    .mp9

So basically I have unlimited records of the same title as long as the media_format is different, the media_format makes them unique.

The records are ordered by title (as you can see in the query) so the unique id is not in order.

How can I do the scrolling cursor, when scrolling forward to get the next 5 records where the lastitle was

lets say I have already loading the first page into my C# app and I then I scroll forward with in the lastitle variable

It will not give me the correct records. so if I change the greater than > to greater than or equal to >= then it will give me 5 records starting with the first occurrence of but I need it to start on record 6

I need to see:

page 1

4    Madonna    <All my love demo>    .mp3
2    Madonna    <Be cool demo>    .wav
7    Madonna    <Like a Virgin>    .aaf
1    Madonna    <Like a Virgin>    .adi
5    Madonna    <Like a Virgin>    .mp4

page 2

11    Madonna    <Like a Virgin>    .wmv
3    Madonna    <Like a Virgin>    .mp5
8    Madonna    <Like a Virgin>    .mp6
10   Madonna    <Like a Virgin>    .mp7
9    Madonna    <Like a Virgin>    .mp8

Solution

Your records must be completely ordered.

If the columns in your WHERE filter (here: singer/title) could identify multiple records, you must add other columns to make the filter unique. This could be the media_format in your case, but if your program doesn't know what other column could be used, it should just use the primary key (track_id or the rowid).

If you sort by multiple columns, the WHERE expression becomes more complex. Take, for example, the following table, sorted by X and Y:

X Y
- -
A 1
A 2  <- last record on previous page
A 3
B 1
B 2

To get all records after the previous record, you have to search for records with the same X value and larger Y values, or with larger X values:

SELECT *
FROM MyTable
WHERE (X = 'A' AND Y > 2)
   OR (X > 'A')
ORDER BY X, Y

In your example, this would be:

SELECT *
FROM tracks
WHERE singer = 'Madonna'
  AND ((title = :lasttitle AND media_format > :lastformat) OR
        title > :lasttitle)
ORDER BY title, media_format
LIMIT ...