Limiting number of unique dates selected in SQLite3 SELECT Query

  python, python-3.x, python-sql, sqlite

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?

Source: Python Questions

LEAVE A COMMENT