SQLite Forum

Querying first of each month
Login
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)
    );