MYSQL: Add a column to result which contains whether this rows contain max number of a particular co-CodePudding

I am using MySQL 5.7 and My Table is:

cp_id	cp_name	cp_version	cp_parent_id
1	playlist1	1	1
2	playlist1	2	1
3	playlist1	3	1
4	playlist2	1	4
5	playlist2	2	4
6	playlist3	1	6
7	playlist3	2	6
8	playlist3	3	6
9	playlist4	1	9

As you can see from the table that:

A single playlist can have more than one version but will have the same parent id.

Result I Require is: I want to add a column to the result which contains whether that row is the cp version row or not.

cp_id	cp_name	cp_version	cp_parent_id	max_version
1	playlist1	1	1	0
2	playlist1	2	1	0
3	playlist1	3	1	1
4	playlist2	1	4	0
5	playlist2	2	4	1
6	playlist3	1	6	0
7	playlist3	2	6	0
8	playlist3	3	6	1
9	playlist4	1	9	1

Thanks, In Advance

CodePudding user response：

On MySQL 8 we can use MAX as an analytic function:

SELECT *, MAX(cp_version) OVER (PARTITION BY cp_parent_id) = cp_verson AS max_version
FROM yourTable
ORDER BY cp_id;

On earlier versions of MySQL we can use a join approach:

SELECT t1.*, t2.cp_version_max = t1.cp_version AS max_version
FROM yourTable t1
INNER JOIN
(
    SELECT cp_parent_id, MAX(cp_version) AS cp_version_max
    FROM yourTable
    GROUP BY cp_parent_id
) t2
    ON t2.cp_parent_id = t1.cp_parent_id
ORDER BY
    t1.cp_id;