Thanks for your reply, yes i can use the limit clause but it would help me greatly if i could also get the offset out of the query. So i can then calculate the next offset and limit. For example:
Name Anna Bob Carl Dan Fred the > 'C%' query gives: Carl Dan Fred which as you've pointed out can be limited, but i'd like to know that the first result is the third row in the set, is this possible? Or do you have to use the count statement? Andrew P.S. Thanks for the hint on the nocase collation. ----- Original Message ----- From: "Dennis Cote" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Thursday, June 22, 2006 5:42 PM Subject: Re: [sqlite] Count and Display > andrew gatt wrote: > > I'm trying to work out the most efficient way of performing a query. > > What i want is for the rest of the database starting at a certain letter, and ordered alphabetically. So for example this command: > > > > SELECT name FROM people WHERE name > 'C%' ORDER BY people > > > > however i need to apply a limit and my application will find this easier if i know of the offset of where the first row is returned. So i think i can get this with: > > > > SELECT COUNT name FROM people WHERE name < 'C%' ORDER BY people > > > > but this is obviously scanning the database twice for the same information, the first query knows the offset of where is starts but i can't think of how to get the information out of the query. Any ideas? Or am i completely barking up the wrong tree? > > > > > > P.S. Is it me or is there no way to do case insenstive commands? > > > Andrew, > > Why can't you simply use the SQL limit clause? > > SELECT name FROM people WHERE name >= 'C%' ORDER BY people LIMIT 20 > > This will get you the first 20 (or fewer) people with names greater than > "C". > > All SQL commands are case insensitive. The following two commands are > identical. > > SELECT name FROM people WHERE name >= 'C%' ORDER BY people LIMIT 20 > > select name from people where name >= 'C%' order by people limit 20 > > HTH > Dennis Cote
