On Mon, Apr 30, 2018 at 7:25 AM, Paul Linehan <linehanp@tcd.ie> wrote:
Now, what I want is to find which illness was reported most in a given month and in a given year.
Basically, you want to computed an ordered listing of all illnesses counts grouped by year-month, then return the first one of them (in the case of ties are you expecting to return two records or one)?
SELECT DISTINCT ON (year, month) year, month, count_of_illness, illness
FROM (
SELECT year, month, count(*) AS count_of_illness, illness
FROM ...
GROUP BY year, month
ORDER BY 1, 2, 3 DESC
) grp
This will return exactly one record, "the first" for each year/month combination in your data. First is determined by the sort in the subquery.
If you need to return multiple records in the case of ties you either, more of less, self-join on (year, month, count) or use something like dense_rank() OVER (partition by year, month order by count_of_illness desc) to assign a rank of 1 to all highest count items and then add a "where dense_rank = 1" filter to the query.
