SQLite Forum

Querying first of each month

Querying first of each month

(1) By bsing (bsingman) on 2020-05-21 08:50:09 [link] [source]


My table has columns: id(string), logfile(string), timestamp(date). I would like to get the filename corresponding to the first entry for each month (based on timestamp) for every unique id (There are multiple entries per id, but I need the first for each month). How might I write this query?


(2) By Stephan Beal (stephan) on 2020-05-21 08:58:42 in reply to 1 [link] [source]

How might I write this query?

You're much more likely to get a useful answer if you post your schema (the SQL, not a description of it) and some sample data.

(3) By luuk on 2020-05-21 10:58:38 in reply to 1 [link] [source]

As Stephan said: more info is needed.

Because (technically): SELECT * FROM .... is also a good answer to your question. (It just gives more data than you want)

You did not even give the tablename, we cannot guess...

(4) By Igor Tandetnik (itandetnik) on 2020-05-22 13:40:47 in reply to 1 [link] [source]

I'm assuming that by "each month" you mean a calendar month.

One way:

select id, logfile, min(timestamp)
from MyTable
group by id, strftime('%Y-%m', timestamp);

This relies on SQLite-specific guarantee whereby, when min() or max() aggregate functions are used, other selected columns are taken from the same row on which the minimal or maximal value is found.

Another way that doesn't rely on this property, but would be much slower on a large dataset:

select * from MyTable t1
where not exists (
  select 1 from MyTable t2
  where t2.id = t1.id and t2.timestamp < t1.timestamp
    and strftime('%Y-%m', t2.timestamp) = strftime('%Y-%m', t1.timestamp)

(5) By anonymous on 2020-05-26 20:37:42 in reply to 4 [link] [source]

Nothing to do with the current topic, but one of the features I miss with the new Forum is the ability to instantly recognize posts by MVP posters, like Igor.

What little I know about SQL, I learned from carefully studying every word Igor ever posted in the "good old days".

A good rule of thumb for you SQLite newbies: if Igor Tandetnik says it, it's right, full stop.

We miss you and your magical powers, Igor. Good to know you still lurk...

(6) By Rob Willett (rwillett) on 2020-05-27 10:40:17 in reply to 5 [link] [source]

I agree 100% with this post. If Igor posts, I always read the response as he posts rarely. I often then go back and read the whole thread. A few other people have my attention as well.

It's kinda ironic that the previoys post is made from somebody called 'anonymous' though :)


(8) By anonymous on 2020-06-25 07:48:24 in reply to 5 [source]

For years my rule of thumb has always been: if Igor talks, you listen

(7) By bsing (bsingman) on 2020-06-20 01:04:28 in reply to 4 [link] [source]

This answer was perfect! Thanks!