CODEWITHSUNDEEP
pythonsqlite
user2532072
user2532072

Reputation: 43

Is rank function supported in python sqlite3

I'm wondering if python sqlite3 library supports RANK() function.

When I execute a query via online Sqlite Browser it works fine, but via python it fails.

File "xyz.py", line 509, in createData
    conn.execute('SELECT RANK() OVER ( ORDER BY dateAndTime)  FROM xyz;')
sqlite3.OperationalError: near "(": syntax error

Thanks in advance!

Upvotes: 2

Views: 2499

Answers (3)

user2532072
user2532072

Reputation: 43

It was pointed out by Shawn that my lib is too old. Br

Sqlite got window functions in 3.25, so you have to be using that or newer.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521513

I don't know if the version of SQLite which your Python script is using supports RANK(). Assuming it does not, here is a query you may use as a workaround:

SELECT
    (SELECT COUNT(*) + 1 FROM yourTable t2
     WHERE t2.id < t1.id) rnk
FROM yourTable t1
ORDER BY
    dateAndTime;

We can use a correlated subquery to handle the rank logic. Note that for DENSE_RANK, it might not be so easy to workaround.

Here is a demo showing that my logic and RANK behave the same way.

Demo

Upvotes: 0

dapaz
dapaz

Reputation: 813

Another alternative is to use row_number() instead:

row_number()

combined with ascending (ASC) or descending (DESC) to get a sorted ranking

ORDER BY DESC # or ASC

Also, this might be useful when working with sqlite in python http://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/

Upvotes: 0

Related Questions