CODEWITHSUNDEEP
mysqlsqlgroup-by
user4035
user4035

Reputation: 23729

How to get GROUP_CONCAT only for rows with maximum value

Suppose, we have a table:

SELECT * FROM users_to_courses;
+---------+-----------+------------+---------+
| user_id | course_id | pass_date  | file_id |
+---------+-----------+------------+---------+
|       1 |         1 | 2014-01-01 |       1 |
|       1 |         1 | 2014-01-01 |       2 |
|       1 |         1 | 2014-02-01 |       3 |
|       1 |         1 | 2014-02-01 |       4 |
+---------+-----------+------------+---------+

Schema:

CREATE TABLE `users_to_courses` (
  `user_id` int(10) unsigned NOT NULL,
  `course_id` int(10) unsigned NOT NULL,
  `pass_date` date NOT NULL,
  `file_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`, `course_id`, `pass_date`, `file_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

One user can pass a certain course multiple times, and every time he passes multiple certificates can be generated. user_id and course_id store the links to users and courses tables. file_id - to files table, where info about certificate files is stored.

In our example user #1 has passed course #1 twice and every time 2 certificates were issued: 4 records totally.

How can I get this data: for user_id=1 for every course get MAX(pass_date) and all the files, attached to this date. So far I could only get this:

SELECT
    users_to_courses.course_id,
    MAX(users_to_courses.pass_date) AS max_passed_date,
    GROUP_CONCAT(users_to_courses.file_id SEPARATOR ',') AS files   
FROM
    users_to_courses
WHERE
    users_to_courses.user_id=1
GROUP BY
    users_to_courses.course_id;

+-----------+-----------------+---------+
| course_id | max_passed_date | files   |
+-----------+-----------------+---------+
|         1 | 2014-02-01      | 1,2,3,4 |
+-----------+-----------------+---------+

I need this:

+-----------+-----------------+---------+
| course_id | max_passed_date | files   |
+-----------+-----------------+---------+
|         1 | 2014-02-01      |   3,4   |
+-----------+-----------------+---------+

I think, this requires a compound GROUP BY.

fiddle

Upvotes: 0

Views: 1529

Answers (1)

bitkot
bitkot

Reputation: 4504

Try the below query it first gets max date for all the records and then we can join only those record in the outer query. You can use the same query for more than one user by adding group by utc.user_id

SELECT
    utc.course_id,
    mdt.maxDate AS max_passed_date,
    GROUP_CONCAT(utc.file_id SEPARATOR ',') AS files
FROM
    users_to_courses utc
    join
(SELECT MAX(pass_date) AS maxDate, course_id cId, user_id uId
        FROM users_to_courses GROUP BY user_id, course_id) AS mdt
ON
    mdt.uId = utc.user_id
AND
    mdt.cId = utc.course_id
AND 
    mdt.maxDate = utc.pass_date
WHERE
    utc.user_id=1
GROUP BY
    utc.course_id;

Upvotes: 1

Related Questions