Select distinct values from the multiple columns one has many values

Question

I have table in this format:

plan_Id	plan_CD	plan_ASSO
2	2	A-2-41557-425898
2	2	A-2-41559-425939_H
2	2	A-2-41560-425939_H
2	2	A-2-41561-425894
2	2	A-2-41563-425932
2	2	A-2-41564-425932
3	3	A-3-76909-425899
3	3	A-3-76909-425899_H
4	4	A-4-41489-425967
4	4	A-4-41524-425967

How to get the result in this format

plan_Id	plan_CD	plan_ASSO
2	2	A-2-41557-425898
3	3	A-3-76909-425899
4	4	A-4-41489-425967

I want to replace plan_ASSO field value A-2-41557-425898 instead of 2 with xx.

If it's always `_H` you could also use `REPLACE(plan_ASSO, '_H', '')` — Barmar, Aug 27 '23 at 06:36
What's the logic to select for example A-2-41557-425898 for plan_Id 2 and not A-2-41561-425894? — Jonas Metzler, Aug 27 '23 at 06:36
See https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql?rq=1 — Barmar, Aug 27 '23 at 06:37
please explain your Problem it doesnt minimums of "question" in the overflow stack are not met https://stackoverflow.com/help/how-to-ask — sep7696, Aug 27 '23 at 06:37
Don't see logic, what about A-2-41561-425894 or A-2-41559-425939_H ? Don't understand what means "I want to replace plan_ASSO field value A-2-41557-425898 instead of 2 with xx." — JCH77, Aug 27 '23 at 07:40

score 2 · Accepted Answer · answered Aug 27 '23 at 07:28

It looks like you want to replace the plan_Id value in the plan_ASSO field with "xx" in the result. You can achieve this using the CONCAT function in MySQL along with a CASE statement to conditionally modify the plan_ASSO field. Here's the SQL query that should give you the desired result:

SELECT
    plan_Id,
    plan_CD,
    CASE
        WHEN plan_ASSO LIKE 'A-%-%-%' THEN CONCAT('A-xx-', SUBSTRING(plan_ASSO, 7))
        ELSE plan_ASSO
    END AS plan_ASSO
FROM
    your_table
GROUP BY
    plan_Id, plan_CD, plan_ASSO
ORDER BY
    plan_Id;

Make sure to replace your_table with the actual name of your table. This query will replace the plan_Id value in the plan_ASSO field with "xx" for rows where the plan_ASSO starts with "A-". For other rows, it will keep the original plan_ASSO value. The GROUP BY clause ensures that you get unique rows for each plan_Id, plan_CD, and modified plan_ASSO combination.

Select distinct values from the multiple columns one has many values

1 Answers1