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?

What's the problem with running Postgres? Do you have very tight memory constraints? Is the installation a problem? — a_horse_with_no_name
Yeah, installation mainly - seems like overkill to require a database server for something that is just a single user application. — kmp
Can't dense_rank be emulated by the code: For couple of thousands of rows it will not even be noticeable. I even think, the overall speed improvement by switching to sqlite will overshadow the additional burden of the rank-counting procedure. — Roman Susi
Installation can be as easy as unzipping an archive. And the resource needs of an idle Postgres server are very low. — a_horse_with_no_name
@a_horse But the resource and administration needs of SQLite are even lower. For simple work. (There's a point where using a server DB makes sense.) — Donal Fellows

LS_ᴅᴇᴠ LS_ᴅᴇᴠ · Accepted Answer · 2013-09-15T21:51:22

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;

dense_rank and sqlite - possible?

2 Answers