[sqlite] Getting row number in a sorted list.

[Simon Slavin]

On 19 Aug 2015, at 1:16pm, Anthrathodiyil, Sabeel (S.) <santhrat at 
visteon.com> wrote:

> Example, for below entries in NameTable
> 
> Name
> 
> 1.       PTN
> 
> 2.       ABCD
> 
> 3.       CDE
> 
> 4.       PQRS
> 
> 5.       AXN
> 
> 
> I want to get the row number of the first name that starts with 'P' in the 
> sorted list. Here it's going to be row number 4 (PQRS)in the sorted list.
> 
> I need the row number, not the entry itself for my use case. How do I form a 
> query to achieve this?

SELECT rowid FROM NameTable
        WHERE name BETWEEN 'P' AND 'PZZZZ'
        ORDER BY name
        LIMIT 1

This will execute faster if you have an index on 'name' in NameTable.

[Yes I know 'PZZZZ' is lazy.  Until you find someone with that name (presumably 
Polish) with that name bite me.]

Simon.