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 ID,name,(select count(*) from t where name<=(select name from t where ID=d.ID)) as Posn from t d order by ID; 1|AAA|1 2|ZZZ|5 3|BBB|2 4|WWW|4 5|CCC|3 sqlite> ________________________________ From: sqlite-users <[email protected]> on behalf of Hegde, Deepakakumar (D.) <[email protected]> Sent: Friday, April 27, 2018 3:51:27 PM To: [email protected] 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
