CODEWITHSUNDEEP
sqlsqlite
Abdul Manaf
Abdul Manaf

Reputation: 4888

Ranking on same data in SQL Lite

I have table as below 

CREATE TABLE test(
   ID INT
);

With Following data

Sample Data

I need following result

ID    Rank
100   1
100   2
105   1
105   2
105   3

I only need to use normal SQL Query no special functions

Upvotes: 1

Views: 130

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

You can use rowid in a correlated query to achieve the result. Since, rowid uniquely identifies as row, we can use it in our case to do correlated counting to assign row number.

Try this:

select id, (
        select count(*)
        from test t2
        where t.id = t2.id
            and t2.rowid <= t.rowid
        ) rank
from test t;

Upvotes: 4

Related Questions