sqlpostgresqlaggregategreatest-n-per-group

SELECT rows with the max count per column value after GROUP BY


Table tmch contains thousands of rows here it is:

CREATE TABLE IF NOT EXISTS public.tmch (
    id bigserial NOT NULL,
    year integer,
    week integer,
    my_number integer,
    device_id bigint,
    CONSTRAINT tmch_pkey PRIMARY KEY (id)
);

Sample data:

1716446 2024    37  13  2
1716447 2024    37  13  2
1716448 2024    37  0   3
1716449 2024    37  11  4
1716450 2024    37  12  4
1716451 2024    37  0   6
1716452 2024    37  0   6
1716453 2024    37  0   6
1716454 2024    37  1   6
1716455 2024    37  1   6
1716456 2024    37  9   7

Here is a query to count how many times my_number occurs for each group of (week, year, device_id):

select count(my_number) c, my_number, device_id, year, week from tmch
group by my_number, device_id, year, week
order by device_id asc, c desc

Result of this query - based on a bigger, different sample:

6   16  2   2024    37
4   17  2   2024    37
4   15  2   2024    37
2   13  2   2024    37
1   2   2   2024    37
8   15  3   2024    37
6   16  3   2024    37
5   14  3   2024    37
3   20  3   2024    37  
3   18  3   2024    37
3   19  3   2024    37
1   8   3   2024    37

How to get only rows with the highest count of my_number per group of (week, year, device_id)?

Result for the sample above:

6   16  2   2024    37  -- because my_number=16 occurs 6 times for device_id=2 y=2024 w=37
8   15  3   2024    37  -- because my_number=15 occurs 8 times for device_id=3 y=2024 w=37

I tried row_number() over (partition by ...) but without success.


Solution

  • DISTINCT ON does what you want, if you use your query as a subquery:

    SELECT DISTINCT ON (device_id, week, year)
           c, my_number, device_id, year, week
    FROM (SELECT count(my_number) AS c,
                 my_number,
                 device_id,
                 year,
                 week
          FROM tmch
          GROUP BY my_number, device_id, year, week) AS sub
    ORDER BY device_id, week, year, c DESC;