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 order by name; select rowid, * from temp.ranked; 1|1|AAA 2|3|BBB 3|5|CCC 4|4|WWW 5|2|ZZZ select rowid from temp.ranked where id = 3; 2 drop table temp.ranked; There is likely a way to do this using a recursive CTE without a temp table, however, I cannot do that off the top of my mind immediately as there are too many possible constraints. Someone else may have already thought about how to do that. There are lots of solutions with various "constraints" and "assumptions" about the data though. This one happens to not require any such assumptions or constraints ... If you, for example, constrained the name column to be unique, then there exists a much simpler solution. Whether or not the sequence: begin immediate; drop table if exists temp.ranked; create table temp.ranked as select * from data order by name; select rowid from temp.ranked where id = 3; drop table if exists temp.ranked; rollback; constitutes a "single sql statement" depends on how you are interfacing with SQLite. For me, it is a single statement returning a single row. YMMV. However, my question would be why you need to know the offset of the ID in the sorted set of results as that seems like a "navigational" problem rather than a "relational" problem? Are the "requirements" perhaps geared to file or hierachical database rather than a relational database? (It is quite common for "navigational" problems to be stated in requirements for "relational" implementations. This is why over the years there have been many functions added to the "monster" relational engines -- because it is easier to add a "MakeCoffee()" function than it is to argue that a relational database should not have a "MakeCoffee" function. The epitome of this is of course Oracle, which has a function for everything you could ever possibly want to do.) --- 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 Hegde, Deepakakumar >(D.) >Sent: Friday, 27 April, 2018 08:51 >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
