Re: [sqlite] Sqlite query to get the offset of an entry in the list

SELECT COUNT(*) FROM TABLE WHERE NAME <= (SELECT NAME FROM TABLE WHERE ID = 3 ORDER BY NAME ASC) Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

How about SELECT ID,NAME, (SELECT COUNT(*) FROM TABLE WHERE NAME<=(SELECT NAME FROM TABLE WHERE ID=d.ID)) as Position FROM TABLE d ORDER BY ID; sqlite> create table t(ID,name text); sqlite> insert into t values (1,'AAA'),(2,'ZZZ'),(3,'BBB'),(4,'WWW'),(5,'CCC'); sqlite> select

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

Again, requiring that both "id" and "name" are candidate keys. In which case, since there has to be unique indexes to enforce that, one might use the more straightforward: select count(*) from table where name <= (select name from table where id=?) order by name; --- The fact that there's

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

The constraint, obviously, being that "id" and "name" are each candidate keys ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users-

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

create table data (id integer primary key, name text); insert into data (name) values ('AAA'), ('ZZZ'), ('BBB'), ('WWW'), ('CCC'); select * from data; 1|AAA 2|ZZZ 3|BBB 4|WWW 5|CCC select * from data order by name; 1|AAA 3|BBB 5|CCC 4|WWW 2|ZZZ create table temp.ranked as select * from data

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

SELECT COUNT(*)+1 FROM TABLE WHERE NAME < (SELECT NAME FROM TABLE WHERE ID = 3) (I think) From: sqlite-users on behalf of Hegde, Deepakakumar (D.) Sent: Friday, April 27, 2018 3:51:27

Re: [sqlite] Sqlite query to get the offset of an entry in the list.

On 27 April 2018 at 15:51, Hegde, Deepakakumar (D.) wrote: > Hi All, > > We have a requirement where in offset of the primary key ID is needed as per > the sorted list. . . . > 1 AAA > 3 BBB > 5 CCC > 4 WWW > 2 ZZZ > > So position of ID 3 as per the sorted

[sqlite] Sqlite query to get the offset of an entry in the list.

Hi All, We have a requirement where in offset of the primary key ID is needed as per the sorted list. Table: ID NAME 1 AAA 2 ZZZ 3 BBB 4 WWW 5 CCC Now need to get the offset of the ID 3 in the sorted list of the NAME. SELECT * FROM TABLE ORDER BY NAME ASC