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 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- >boun...@mailinglists.sqlite.org] On Behalf Of x >Sent: Friday, 27 April, 2018 09:43 >To: SQLite mailing list >Subject: 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 <sqlite-users-boun...@mailinglists.sqlite.org> on >behalf of Hegde, Deepakakumar (D.) <deep...@allgosystems.com> >Sent: Friday, April 27, 2018 3:51:27 PM >To: sqlite-users@mailinglists.sqlite.org >Subject: [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 > > >1 AAA > >3 BBB > >5 CCC > >4 WWW > >2 ZZZ > > >So position of ID 3 as per the sorted list of the NAME is 2. > > >currently we are getting the entry with the select statement and by >comparing the ID externally we are getting the offset. > > >Is there any optimal way to get this information directly with one >single query? > > >Thanks and Regards > >Deepak > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
