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? Thanks, Sabeel -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John McKown Sent: Wednesday, August 19, 2015 6:32 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Getting row number in a sorted list. 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); insert into NameTable(Name) sqlite> Values('ABCD'); insert into NameTable(Name) Values('CDE'); sqlite> insert into NameTable(Name) Values('PQRS'); insert into sqlite> NameTable(Name) Values('AXN'); SELECT rowid, Name from sqlite> 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'); select rowid, Name sqlite> from NameTable; 1|ABCD 2|CDE 3|PQRS 4|AXN 5|AXN2 sqlite> insert into NameTable(Name) Values('PQRS2'); select rowid, Name sqlite> 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'; select rowid, Name from sqlite> 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 _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
