CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Brunoop
Brunoop

Reputation: 178

How to retrieve first row of the week for each week

I have a table that looks like that

foo_date   | bar 
---------------
2018-01-01 | bar
2018-01-09 | bar
2018-01-10 | bar
2018-01-20 | bar

And I would like to build a request that retrieves,for each week, the row which occurs first in the week.

Cheers

Upvotes: 0

Views: 72

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can simply do:

select distinct on (datetrunc('week', foo_date)) t.*
from t
order by datetrunc('week', foo_date), foo_date;

Upvotes: 2

S-Man
S-Man

Reputation: 23726

demo:db<>fiddle

SELECT DISTINCT ON (year, week)
    foodate, bar
FROM (
    SELECT
        foodate,
        bar,
        EXTRACT('isoyear' FROM foodate) as year,
        EXTRACT('week' FROM foodate) as week
    FROM dates
    ORDER BY foodate
)s

EXTRACT('week'...) gives the week. So two date in the same week give the same output at this column.

DISTINCT ON (week) gives the first (ordered!) row for each week.

Postgres Date functions

Notice the definition of the week:

The number of the ISO 8601 week-numbering week of the year. By definition, ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year.

Edit: If you have data from more then a year of course you should add the year as well. Other wise you get the first row of all first weeks of all years for example.

Upvotes: 2

Related Questions