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

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;