MYSQL: Add a column to result which contains whether this rows contain max number of a particular column or not

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

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating