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

Date	Count
2022-01-25	24
2022-02-08	50
2022-03-09	32

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

Date	Count
2022-01-25	24
2022-02-08	50
2022-03-09	32

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

Share Improve this question Follow edited Mar 14 at 11:52 Zegarek 27.4k55 gold badges2424 silver badges3030 bronze badges asked Mar 14 at 11:37 OwenOwen 1311 silver badge44 bronze badges 5

• 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

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

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";

Date	Count
2022-01-25	24
2022-02-08	50
2022-03-09	32

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:

dts	amt
2025-01-11	87
2025-02-04	99
2025-03-16	80