How can I count the number of rows before a certain row in sqlite?

Question

I have a database that stores the rank of an Item. The rank is an absolute value that will be correct if all the items are taken into account.

If I only need a subset say four of this items it will give me something like:

Rank     RowId in the whole Table
---------------
4         114 
8         71
70        16
83        7

I now need an int specifying the rank only in the subset where the max rank is the number of items in the subset in my example 1,2,3,4.

Is there a way to achieve this in my sqlite query? I only need one of the ranks in my Activity. I thought of ordering the results of the query by rank and then somehow get the position of item I want to rank at that moment. But how would I achieve this with sqlite?

I tried to create a temporary table and insert the subset into it like this:

CREATE TABLE rank(ID); INSERT INTO position SELECT ID from items WHERE ITEM_ID = 3 ORDER BY POSITION; SELECT RowID from rank WHERE ID = 9; DROP TABLE rank;

This is working in SQLite Manager and will return the correct number. But if I do this in Android in fails saying that there is no table rank while compiling query

07-07 13:35:46.150: ERROR/AndroidRuntime(2047): Caused by: android.database.sqlite.SQLiteException: no such table: rank: , while compiling: SELECT RowID from rank WHERE ID = 9

Answer 1

EDIT: have to agree with @Matt the only way I've been able to do this is to use the temp table approach.

For what it's worth here's what it looks like...

create temp table if not exists <temptable>(Id integer primary key, rank); 
insert into temptable(rank) select <column> from <table>; 
select * from temptable; 

EDIT: Actually that returns the ID associated with the row which isn't sequential so you won't always get 1,2,3,4... I'll have to think of something else. Sorry.

Not sure if I've understood your question. You basically want this?

Id        Value
---------------
1         4
2         8 
3         70
4         83

So you want to add a pseudo-column as the id no matter what your subset contains?

If that's correct then this should do it...

SELECT RowId, <other columns>.... FROM <table> WHERE <where>

Apologies if I've misunderstood.

Answer 2

You could output your query (ordered by rank) into a temporary table with an auto increment ID.

Answer 3

If you need to read only one row from a subquery you can always execute a limit on it, by providing the offset of how many records to be skipped first, and how much to be returned

so if you want to get 25th row you tell to skip 24, and return 1

select * from (SELECT * FROM table order by rank) limit 24,1