How do I query a SQL database to return the first "page" containing the specified data?

Question

------------------------
|      |     A   |     |
------------------------
|      |     B   |     |
------------------------
|      |     C   |     |
------------------------ -----Page 1
|      |     D   |     |
------------------------
|      |     E   |     |
------------------------
|      |     F   |     |
------------------------ -----Page 2
|      |     F   |     |
------------------------
|      |     F   |     |
------------------------
|      |     G   |     | -----Page 3

Please excuse my ascii art, never done it before.

I have a database with the second colunm sorted as shown above. The database displays to the user in "pages" of 3 rows each.

The user is then going to seach (via a text input) for some data from the sorted second column and wants the page returned that has the first occurance of that data.

For example, the user enters "F", page 2 is returned as it contains the first occurance of F in this sort order.

For example, the user enters "C", page 1 is returned as it contains the first (and only) occurance of C in this sort order.

What is the query to do this?

My first thought is to find the row of the first occurance, calculate what page it is in and then query that page as normal. Is that the most efficient way, or is there some build in functionality.

Thank you.

-SQLite (via C++ API)
-Visual Studio 2003.NET

Answer 1

I think your first thought was the correct one. This is because is it not very regular to have "hidden info" in your database, and base queries on that. Your hidden info here is on which page something is, which is merely based on which row it is if sorted by a certain column.

There are different ways to approach this, though. The most common one, I think, would be not to display page 2 when a user searches for F, but to display page 2.66 (which also has three items). It has some drawbacks, but it also has advantages over your method. Another way would be to add a column to the table which describes on which page something is. This has the disadvantage that as long as you are sticking to the "formula division" it is redundant data. It also is problematic if data is added to any other place than the "end" of the table. It does have the added flexibility that you can decide to give a certain page an extra item.

Answer 2

I have a database with the second colunm sorted as shown above.

No, you don't. SQL tables are unordered. What you have is a SELECT result that just happens to be in ROWID order as an implementation detail. If you want to base queries on the row number, then it should be an explicit column:

CREATE TABLE T(
    ID        INTEGER PRIMARY KEY AUTOINCREMENT,
    Letter    CHAR
);