dense_rank and sqlite - possible?

Question

My situation is that I have a PostgresSQL database that is pretty small (about 10 tables, biggest of which is a couple of thousand rows) and I want to use it from a single-user style application so having to install a whole server feels unnecessarily onerous. So, I thought, well SQLite sounds like it will be just the ticket.

I ported over the schema, imported the data and then I got stuck on a view (actually, the only view it has). My problem is that I am using the ever so handy dense_rank function. I read this: sqlite - Unsupported Sql Analytical Functions and can see that it is not available but it tipped me off that it may be in SQLite::More but it doesn't seem to be in there either.

So, does anyone know of any way I can get the dense_rank function to be available in a view inside a SQLite database? Is there another library I can use perhaps?

Answer 1

You can emulate DENSE_RANK function with sub-queries:

Example:

SELECT Products.Product,
DENSE_RANK() OVER (ORDER BY Products.Code DESC) AS Rank
FROM Products;

In SQLite:

SELECT Product,
(SELECT COUNT()+1 FROM (
    SELECT DISTINCT Code FROM Products AS t WHERE Code < Products.Code)
) AS Rank
FROM Products;

Answer 2

Update 2018: If you look at the sqlite version history dense_rank (among other window functions) is supported as of version 3.25.

https://www.sqlite.org/changes.html