Limiting number of unique dates selected in SQLite3 SELECT Query

So basically, this is a SQLite3 table that I have: Click Here for Image

As you can see in the image, there are 8 distinct dates, going from 29/07/2021 to 05/08/2021. I want to run a SQLite3 Query that returns only the latest 7 out of these 8 dates (i.e. 30/07/2021 to 05/08/2021) BUT it returns all the rows for each date.

I have tried using the LIMIT keyword like this:

all_app_data = sorted(conn.execute('SELECT date, application, usage_time FROM monitor '
                                   'ORDER BY date DESC, usage_time ASC LIMIT 7').fetchall())

However, this only picks up 1 row from each date (i.e. 1 row from 30/07/2021 instead of the 5 rows present). How can I fix this?

Answer

You can do it with DENSE_RANK() window function:

SELECT date, application, usage_time 
FROM (
  SELECT *, DENSE_RANK() OVER (ORDER BY date DESC) rn
  FROM monitor
) 
WHERE rn <= 7
ORDER BY date DESC, usage_time ASC

Advertisement

Answer