On Wed, Aug 19, 2015 at 7:40 AM, Simon Slavin <slavins at bigfraud.org> wrote:
>
> 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.
>
?Interesting. What happens if somebody did: CREATE TABLE NameTable (Name
text PRIMARY KEY) WITHOUT ROWID ??
?Also, what about the following:
sqlite> create table NameTable (Name TEXT);
sqlite> insert into NameTable(Name) Values('ABCD');
sqlite> insert into NameTable(Name) Values('CDE');
sqlite> insert into NameTable(Name) Values('PQRS');
sqlite> insert into NameTable(Name) Values('AXN');
sqlite> SELECT rowid, Name from NameTable;
1|ABCD
2|CDE
3|PQRS
4|AXN
sqlite> select rowid from NameTable where Name between 'P' and 'PZZZZZZZ'
limit 1;
3
sqlite> insert into NameTable(Name) Values('AXN2');
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
3|PQRS
4|AXN
5|AXN2
sqlite> insert into NameTable(Name) Values('PQRS2');
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
3|PQRS
4|AXN
5|AXN2
6|PQRS2
sqlite> delete NameTable where Name='PQRS';
Error: near "NameTable": syntax error
sqlite> delete from NameTable where Name='PQRS';
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
4|AXN
5|AXN2
6|PQRS2
sqlite> select rowid from NameTable where Name between 'P' and 'PZZZZZZZ'
limit 1;
6
Hum, that probably isn't what the OP wanted. I would guess in this latter
table, he would want "5" because that is the _relative_ row number you
would see by counting if you did a simple "SELECT Name FROM NameTable;"
The basic problem is that the question is "improper". In general, there is
no "relative row number" for the rows listed by a "SELECT" command. Oh,
there is when you look at it. But the order of the rows returned where then
is no ORDER BY clause is not guaranteed. And, even then, the order of
individual rows which have the same ordering with the ORDER BY are not
guaranteed. What I mean is, if you do SELECT A,B,C FROM TABLE ORDER BY A, B
; and there are two or more rows with equal A & B values, then the order of
the C values is not guaranteed.
So, what to do? Well, it would be possible to do something like:
sqlite> drop table if exists row_order;
sqlite> create temporary table row_order AS select * from NameTable;
sqlite> select rowid from row_order where Name between 'P' and 'PZZZZZZZ'
limit 1;
5
sqlite> select rowid, Name from row_order;
1|ABCD
2|CDE
3|AXN
4|AXN2
5|PQRS2
sqlite> select rowid, Name from NameTable;
1|ABCD
2|CDE
4|AXN
5|AXN2
6|PQRS2
sqlite>
This may answer the OP's question. But it only works for SQLite. And I am
not sure that it is guaranteed to work on all past and future versions of
SQLite. It depends on the non-standard ROWID facility in SQLite. Perhaps
Dr. Hipp can address this last issue.
?
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
