CODEWITHSUNDEEP
sqldatabasesqlite
InsaneCoder
InsaneCoder

Reputation: 8278

Get offset of a row after performing sort operation in sql

I am using SQLite database.

Suppose I have rows with IDs 1 to 50. Then I perform select and order by operation.

Say, the result is IDs : 6,3,5,2,9,12,1,34,45,15.

Now, I want to know the offset of a particular row with given ID in the above result.e.g. offset of ID 1 is 6.

Can I do this in a single query?

Upvotes: 1

Views: 58

Answers (1)

void
void

Reputation: 7890

put the query of ordered result into a subquery and use count(*) and check the id sequence:

Example:

SCHEMA:

CREATE TABLE tbl ("id" INTEGER,"val" INTEGER);
INSERT INTO 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(*) 
           from (
                 select id,val 
                 from tbl order by val
                 ) b  
           where a.val >= b.val)-1 as offset
from tbl a
order by 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

Upvotes: 1

Related Questions