SamohtVII SamohtVII - 18 days ago 5
MySQL Question

Getting MAX of column x while getting DISTINCT on column y

I want to join these tables (task_id with post_id) which will return everything you see below. That's fine but what I want to do next is to remove duplicates of task_id while keeping the item that has the highest version.

I have 2 tables like so:

items

ID title task_id project_id version
1 "Test" 123 456 1
2 "Test 2" 124 456 1
3 "Test 3" 125 456 1
4 "X 3.1" 125 456 1.1
5 "X 3.2" 125 456 1.2


tasks

ID post_id meta_key meta_value
1 123 _completed 0
4 124 _completed 0
5 125 _completed 0


and I have this SQL statement so far:

SELECT *
FROM items t0
INNER JOIN tasks AS t1
ON t0.task_id = t1.post_id
WHERE t1.meta_key = '_completed'
AND project_id = 456


which returns:

ID title task_id project_id version ID post_id meta_key meta_value
1 "Test" 123 456 1 1 123 _completed 0
2 "Test 2" 124 456 1 1 124 _completed 0
3 "Test 3" 125 456 1 1 125 _completed 0
4 "X 3.1" 125 456 1.1 1 125 _completed 0
5 "X 3.2" 125 456 1.2 1 125 _completed 0


How can I remove duplicate task_id's but keep the highest version so the table will be:

ID title task_id project_id version ID post_id meta_key meta_value
1 "Test" 123 456 1 1 123 _completed 0
2 "Test 2" 124 456 1 1 124 _completed 0
5 "X 3.2" 125 456 1.2 1 125 _completed 0


Basically I am closest by ORDERing them BY version and the using GROUP BY task_id but apparently SQL does not let you do this.

Can anyone think of a good way to achieve this?

Answer

Based on a related post I think this should work.

SELECT *
FROM items t0
INNER JOIN tasks AS t1 ON t0.task_id = t1.post_id
INNER JOIN
    (SELECT task_id, MAX(version) AS MaxVersion
    FROM items
    GROUP BY task_id) groupedt0 
ON t0.task_id = groupedt0.task_id 
AND t0.version = groupedt0.MaxVersion
AND project_id = 456
AND t1.meta_key = '_completed'
ORDER BY ID ASC

See: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

Comments