The traditional solution, the one you may find in books, is to do a self join: first find that "max date per group" of yours, then join to self table on rows with said max date.
However, some hacks allow you to avoid that. Consider the following query:
SELECT
MAX(date_added) AS date_added,
SUBSTRING_INDEX(GROUP_CONCAT(some_column ORDER BY date_added DESC), ',', 1) AS some_column,
SUBSTRING_INDEX(GROUP_CONCAT(another_column ORDER BY date_added DESC), ',', 1) AS another_column
FROM t
GROUP BY whatever
GROUP_CONCAT
is an aggregation function which implodes values onto one string. It allows for ORDER BY
, which we utilize via ORDER BY date_added DESC
so as to implode our desired value first. We then slice up the first token in the string via SUBSTRING_INDEX
.
The downside here (apart from making the query quite the frightening appearance) is that your numerical values are transformed into texts. Typically no big deal with SQL, but please be aware.
See also my old post: Selecting a specific non aggregated column data in GROUP BY
There's another option where you do a semi-self-join, a much lighter one; you will have to give up usage of index. It's quite long to describe; it still uses GROUP_CONCAT
and SUBSTRING_INDEX
, but only for the purpose of creating a derived table with only relevant keys. This derived table is then joined with original table. See an example in SQL: selecting top N records per group, another solution.
SELECT
SUM(IF(user_type='I',1,0)) Individual_Count,
SUM(IF(user_type='G',1,0)) Group_Count,
DATE_FORMAT(dt,'%M') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;
or
SELECT
SUM(user_type='I') Individual_Count,
SUM(user_type='G') Group_Count,
DATE_FORMAT(dt,'%M') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;
If you would like the counts summed up, include WITH ROLLUP
SELECT
SUM(IF(user_type='I',1,0)) Individual_Count,
SUM(IF(user_type='G',1,0)) Group_Count,
IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;
or
SELECT
SUM(user_type='I') Individual_Count,
SUM(user_type='G') Group_Count,
IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;
Best Answer
According to your SQL statement you always get a unique date from a group VesseleID since you are using MAX from group. By using join statement on date you can get your desired result.
Explanation:
Here is the data set respective to the SQL statement #1, #2, #3.