Retrieving Unique Code Values with Corresponding Dates and Totals in MySQL

The author presents a common requirement: fetch unique code values while still returning the associated cdate and ctotal columns from a table tt .

Using DISTINCT on a single column works for simple de‑duplication, e.g., select distinct code from tt; , but when multiple columns are listed after DISTINCT , the distinctness applies to the combination of all those columns, not just code :

select distinct code, cdate, ctotal from tt;

Placing DISTINCT after other selected columns causes a syntax error, for example:

select cdate, ctotal, distinct code from tt;

Similarly, a naïve GROUP BY code query fails under MySQL's ONLY_FULL_GROUP_BY mode because non‑aggregated columns ( cdate , ctotal ) are not functionally dependent on code :

select code, cdate, ctotal from tt group by code;

To work around this, the session sql_mode can be adjusted to remove ONLY_FULL_GROUP_BY :

show variables like '%sql_mode%';

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

After disabling the strict mode, the same GROUP BY query returns the desired rows:

select code, cdate, ctotal from tt group by code;

Another approach uses GROUP_CONCAT together with DISTINCT to aggregate unique codes while still selecting the date and total columns:

select group_concat(code), cdate, ctotal from tt group by code;

Adding DISTINCT inside GROUP_CONCAT eliminates duplicate codes:

select group_concat(distinct code), cdate, ctotal from tt group by code;

Each method has trade‑offs: removing ONLY_FULL_GROUP_BY may produce nondeterministic cdate and ctotal values for duplicated codes, while GROUP_CONCAT concatenates all codes into a single string. The choice should depend on the specific scenario and data correctness requirements.