database - Get offset of a row after performing sort operation in sql -

i using sqlite database.

suppose have rows ids 1 50. perform select , order by operation.

say, result ids : 6,3,5,2,9,12,1,34,45,15.

now, want know offset of particular row given id in above result.e.g. offset of id 1 6.

can in single query?

put query of ordered result subquery , use count(*) , check id sequence:

example:

---

schema:

create table tbl ("id" integer,"val" integer); insert tbl ("id","val") values     (12,6),(1,7),(34,8),(6,1),(9,5),     (45,9),(15,10),(3,2),(5,3),(2,4); 

query:

select id,(            select count(*)             (                  select id,val                   tbl order val                  ) b              a.val >= b.val)-1 offset tbl order offset 

result:

id  offset 6   0 3   1 5   2 2   3 9   4 12  5 1   6 34  7 45  8 15  9 

sqlfiddle demo