CODEWITHSUNDEEP
postgresql
Andrew Fox
Andrew Fox

Reputation: 133

Extract highest date per month from a list of dates

I have a date column which I am trying to query to return only the largest date per month.

What I currently have, albeit very simple, returns 99% of what I am looking for. For example, If I list the column in ascending order the first entry is 2016-10-17 and ranges up to 2017-10-06.

A point to note is that the last day of every month may not be present in the data, so I'm really just looking to pull back whatever is the "largest" date present for any existing month.

The query I'm running at the moment looks like

SELECT MAX(date_col)
  FROM schema_name.table_name
 WHERE <condition1>
   AND <condition2>
 GROUP BY EXTRACT (MONTH FROM date_col)
 ORDER BY max;

This does actually return most of what I'm looking for - what I'm actually getting back is

"2016-11-30"
"2016-12-30"
"2017-01-31"
"2017-02-28"
"2017-03-31"
"2017-04-28"
"2017-05-31"
"2017-06-30"
"2017-07-31"
"2017-08-31"
"2017-09-29"
"2017-10-06"

which are indeed the maximal values present for every month in the column. However, the result set doesn't seem to include the maximum date value from October 2016 (The first months worth of data in the column). There are multiple values in the column for that month, ranging up to 2016-10-31.

If anyone could point out why the max value for this month isn't being returned, I'd much appreciate it.

Upvotes: 1

Views: 644

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

You are grouping by month (1 to 12) rather than by month and year. Since 2017-10-06 is greater than any day in October 2016, that's what you get for the "October" group.

You should

GROUP BY date_trunc('month', date_col)

Upvotes: 2

Related Questions