CODEWITHSUNDEEP
postgresql
joaumg
joaumg

Reputation: 1248

First record by month & by year

I have a Rails application with 20+ years of data.

I'm struggling to create two SQLs:

  1. Fetch the first record of each year (based on filters)
  2. Fetch the first record of each month (based on filters)

I made a DBFiddle here: https://www.db-fiddle.com/f/wjQqrrpaJeiYG8zkExbaos/0

For the first query (yearly), the result should be:

a     | b_id  | created_at
74780 | 82373 | 2020-01-02 01:34:33 +0000
15670 | 16639 | 2019-02-24 14:33:56 +0000
14586 | 87594 | 2018-01-06 09:14:31 +0000

I can fetch the years and months using date_part('year', created_at) and date_part('month', created_at), but didn't find a way to "glue" them with min(created_at).

Upvotes: 1

Views: 56

Answers (1)

Renato
Renato

Reputation: 2157

Try to use window function OVER:

with grouped as(
       select *, min(created_at)  over(partition by  date_trunc('year', created_at)) 
       from z  order by date_trunc('year', created_at) desc
     )
select a, b_id, created_at from grouped where min = created_at

For the first record by month you can use the same approach by replacing all date_trunc('year', created_at) with date_trunc('month', created_at)

Upvotes: 2

Related Questions