On 2015-08-20 07:44 AM, Anthrathodiyil, Sabeel (S.) wrote: > Hi, > I think there is ambiguity in the example I provided, I wouldn't need a > ranking in my case as I need the offset (think row number is misleading) of > the first record in the sorted list. > > Here is my use case. > Find the rownumber 'y' (offset) for a name in the name table which starts > with given letter.(table shall be sorted in name order) > In subsequent operations I will pass this offset 'y' and number of names 'x' > to be retrieved (limit 'x' offset 'y') on the sorted list. > > As you suggested, I can create a temp table in the sorted order and do a > MIN(ROWID) for the name that I am looking for. > Is there any better alternatives? Or is there any caveats here?
I trust you've noticed the other replies which explain how to achieve the above, however, the way you describe things to work is not advisable. When you have a nicely ordered Index (as you seem to have), this query format: SELECT ... FROM ... WHERE Name LIKE 'P%'; Is several magnitudes faster than: SELECT ... FROM ... LIMIT x,y; And they will both return the exact same results in the exact same order (when done correctly). You should never use LIMIT / OFFSET to facilitate result windowing. If you are not familiar with why it is faster, a quick explanation might be: Imagine someone asked you to find all the people in a phonebook whose Surname starts with P. The first query is like when you open the book just past half-way, look for the alphabetically first "P...." entry and then start reading the names until you find the first name that does not start with "P". This is quite fast. The second query (with OFFSET/LIMIT) is if you open the phonebook at person 1, then scan every name one by one but only start reading /calling out the names when you get to name number 13724 (or whichever one is the first "P%" name). This is very much slower. What is worse, if your LIMIT is by some accident 1 more than the amount of "P%" names, then you have to keep reading through the phonebook until the very end. Put another way: OFFSET, LIMIT and ORDER BY are not direct Query functions but in stead are functions re-processing (or post-processing if you will) a result-set already returned by the main query. You should always use the query's filtering capabilities (i.e. the WHERE clause) to lessen the list of rows. HTH, Ryan
