How to skip a record if the date is repeating in the MySQL table?

Question

I am facing an issue with a moderately simple-looking SELECT SQL Query.

My requirement: For One specific company, if there are multiple records with the same date, I want only the first record of that date, not all from that date.

Given table:

User	company	joining_date
Devika	Google	1/3/2021
Aparna	Apple	12/9/2021
Suresh	Google	10/2/2022
Rajesh	Apple	12/9/2021
Arun	Google	10/2/2022

Expected Output:

count	users	date	company
2	Devika,Suresh	1/3/2021,10/2/2022	Google

My Output:

count	users	date	company
3	Devika,Suresh, Arun	1/3/2021,10/2/2022	Google

My Approach:

select 
    count(user) as count, 
    group_concat(DISTINCT user) as users, 
    group_concat(DISTINCT date) as date
    company
from employee 
GROUP by company 
having company = "Google";

As per the requirement, I grouped the records by company name. I only want the results for Google, so I added a condition for that. Now, if I use the Distinct keyword, I will not get duplicate dates, but I will still have three counts of users instead of two, and three usernames instead of two. I want to skip "Arun" from this result.

How can I achieve this logic?

Answer 1

Assuming you're using MySql 8+ the ubiquitous approach is to use row_number to produce the desired rows before you aggregate. The order by critereia determins which is the "first" row in each partition (in this case rows with the same date):

select count(user) as count, 
    group_concat(user) as users, 
    group_concat(joining_date) as date,
    company
  from (
    select *, Row_Number() over(partition by joining_date order by user desc) rn
    from t
    where company = 'Google'
)t
where rn = 1
group by company;

See this example Fiddle

Answer 2

Although, Stu's answer is correct. After some trial and error, I found a much easier way to achieve the desired result where the window function is not required.

My New Approach:

select 
    count(tmp.id) as count, 
    group_concat(tmp.user) as users, 
    group_concat(tmp.joining_date) as date, 
    tmp.company as company 
from 
    (
        select * from employee 
        where company = "Google" 
        group by joining_date
    ) as tmp 
group by tmp.company;

As per this approach, we can use subquery first where we apply conditions for the company and group it by the joining date to ignore the second record with duplicate joining date. Then into the main query, we group by with the company to get the desired result.

Note: To make this work, the default config for ONLY_FULL_GROUP_BY has to be disabled.

Answer 3

You can utilize a subquery or CTE to achieve the filtering and then perform the GROUP_CONCAT separately.

SELECT
    COUNT(user) AS count,
    GROUP_CONCAT(user) AS users,
    GROUP_CONCAT(joining_date) AS date,
    company
FROM (
    -- Subquery starts here
    SELECT
        user,
        joining_date,
        company,
        ROW_NUMBER() OVER (PARTITION BY company, joining_date ORDER BY user) AS row_num
    FROM employee
    WHERE company = "Google"
    -- Subquery ends here
) AS subquery
WHERE row_num = 1
GROUP BY company;

1. The subquery selects the necessary columns from the employee table and adds a row_num column using the ROW_NUMBER() window function. The PARTITION BY clause ensures that the numbering is done separately for each company and joining date.
2. The WHERE clause of the subquery filters the results to only include records for the company "Google".
3. The outer query then filters the results from the subquery to only include rows with row_num equal to 1. This effectively selects the first record for each company and joining date combination.
4. Finally, the outer query uses GROUP_CONCAT to concatenate the user names and joining dates for the selected records, and the results are grouped by the company.

This approach brings the strengths of SQL's window functions, filtering capabilities, and grouping functionalities to efficiently produce the desired output (specific requirement of skipping duplicate dates for the specified company).