I have a table with two columns: _{demo at db<>fiddle}

create table your_table("Date","Count")as values
 ('2022-01-13'::date,   8)
,('2022-01-18'::date,   14)
,('2022-01-25'::date,   24)
,('2022-02-08'::date,   50)
,('2022-02-15'::date,   15)
,('2022-03-01'::date,   6)
,('2022-03-09'::date,   32)
,('2022-03-15'::date,   10);

and I want to get the following result. Max amount per day for a month

I have a table with two columns: _{demo at db<>fiddle}

create table your_table("Date","Count")as values
 ('2022-01-13'::date,   8)
,('2022-01-18'::date,   14)
,('2022-01-25'::date,   24)
,('2022-02-08'::date,   50)
,('2022-02-15'::date,   15)
,('2022-03-01'::date,   6)
,('2022-03-09'::date,   32)
,('2022-03-15'::date,   10);

and I want to get the following result. Max amount per day for a month

I can’t group the query so that the result is months and days with the maximum count

• sql
• postgresql
• group-by
• window-functions
• distinct-on

• 1 distinct on(date_trunc('month',date)) date, count from your_table order by date_trunc('month',date), count desc; – Zegarek Commented Mar 14 at 11:42
• 1 @Zegarek Good option for month only across all years, but if we want YYYY-MM then maybe we need window functions. – Tim Biegeleisen Commented Mar 14 at 12:03
• @TimBiegeleisen date_trunc('month'.. truncates away anything below months, so it keeps the year in there if that's what you had in mind - the result is the full date moved back to the first of the month. The only difference here is that it does that while staying in date type, without having to convert to text. You can also use it with window functions, same effect – Zegarek Commented Mar 14 at 12:19
• @TimBiegeleisen I just realised you might've meant date_part() which works like extract() - the date_trunc() I used isn't like that - it truncates below the target field, it keeps the rest. For 'month' it keeps the year and the month. – Zegarek Commented Mar 14 at 13:10
• What is the desired behavior if more than one day in a month has the maximum count? – JohnH Commented Mar 14 at 15:48

2 Answers 2

We can use ROW_NUMBER() (or RANK() in case we want all ties) along with a partition over month and year:
_{demo at db<>fiddle}

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY TO_CHAR("Date", 'YYYYMM') ORDER BY "Count" DESC) rn
    FROM your_table)
SELECT "Date", "Count"
FROM cte
WHERE rn = 1
ORDER BY "Date";

PostgreSQL also offers a DISTINCT ON clause that returns the top row per group:

select distinct on(date_trunc('month',"Date")) 
       "Date"
     , "Count"
from your_table 
order by date_trunc('month',"Date")
       , "Count" desc;

Result is the same but this construct is RDBMS-specific.

Try using ROW_NUMBER() and CTE.

Table:

create table test_dt 
(
    dts date,
    amt int
);

Sample data:

insert into test_dt values('2025-03-14',10),('2025-03-11',20),('2025-03-13',19),('2025-03-16',80); 
insert into test_dt values('2025-02-01',10),('2025-02-03',40),('2025-02-08',15),('2025-02-04',99); 
insert into test_dt values('2025-01-17',10),('2025-01-13',60),('2025-01-11',87),('2025-01-21',16);  

Query:

with cte as 
( 
 select *,row_number() over(partition by to_char(dts,'YYYY-MM') order by amt desc) rnk  
 from test_dt 
) 
select dts,amt  
from cte c 
where rnk = 1;

Output: