Hi, and thanks for taking the time to reply
I used this (helped by your original query):
SELECT
t3.c_year AS "Year",
t3.c_month AS "Month",
t3.il_mc AS "Tumour count",
t4.ill_nat AS "Type" FROM
(
SELECT c_year, c_month, il_mc FROM
(
SELECT
c_year,
c_month,
MAX(month_count) AS il_mc
FROM
(
SELECT nature_of_illness as illness,
EXTRACT(YEAR FROM created_at) AS c_year,
EXTRACT(MONTH FROM created_at) AS c_month,
COUNT(EXTRACT(MONTH FROM created_at)) AS month_count
FROM illness
GROUP BY illness, c_year, c_month
ORDER BY c_year, c_month
) AS t1
GROUP BY c_year, c_month
) AS t2
) AS t3
JOIN
(
SELECT
EXTRACT(YEAR FROM created_at) AS t_year,
EXTRACT(MONTH FROM created_at) AS t_month,
nature_of_illness AS ill_nat,
COUNT(nature_of_illness) AS ill_cnt
FROM illness
GROUP BY t_year, t_month, nature_of_illness
ORDER BY t_year, t_month, nature_of_illness
) AS t4
ON t3.c_year = t4.t_year
AND t3.c_month = t4.t_month
AND t3.il_mc = t4.ill_cnt
and got this as a result:
SELECT
t3.c_year AS "Year",
t3.c_month AS "Month",
t3.il_mc AS "Tumour count",
t4.ill_nat AS "Type" FROM
(
SELECT c_year, c_month, il_mc FROM
(
SELECT
c_year,
c_month,
MAX(month_count) AS il_mc
FROM
(
SELECT nature_of_illness as illness,
EXTRACT(YEAR FROM created_at) AS c_year,
EXTRACT(MONTH FROM created_at) AS c_month,
COUNT(EXTRACT(MONTH FROM created_at)) AS month_count
FROM illness
GROUP BY illness, c_year, c_month
ORDER BY c_year, c_month
) AS t1
GROUP BY c_year, c_month
) AS t2
) AS t3
JOIN
(
SELECT
EXTRACT(YEAR FROM created_at) AS t_year,
EXTRACT(MONTH FROM created_at) AS t_month,
nature_of_illness AS ill_nat,
COUNT(nature_of_illness) AS ill_cnt
FROM illness
GROUP BY t_year, t_month, nature_of_illness
ORDER BY t_year, t_month, nature_of_illness
) AS t4
ON t3.c_year = t4.t_year
AND t3.c_month = t4.t_month
AND t3.il_mc = t4.ill_cnt
> 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.
Not what I required - there's no point in having ties randomly returning.
> 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.
Can't use DENSE_RANK() - MySQL 5.6 doesn't support it :-(
Thanks again for your help!
Rgs,
Pól...
> David J.
