Question 112724 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Setting alias name as per timestamp

I am trying to set an alias name for column challenge_name especially when I encounter ‘challenge’ based on timestamp/date as challenge 1, challenge 2, etc. If it’s not ‘challenge’ I would like to set the column name the same as challenge_name

This is my current input

+-----------+----------------+
|   date    | challenge_name |
+-----------+----------------+
| 1/11/2022 | find the bug   |
| 1/12/2022 | challenge      |
| 1/13/2022 | kill pill      |
| 1/14/2022 | hello copter   |
| 1/15/2022 | challenge      |
| 1/16/2022 | miami          |
| 1/17/2022 | hello world    |
| 1/18/2022 | challenge      |
| 1/19/2022 | dominc         |
| 1/20/2022 | challenge      |
+-----------+----------------+

This is my expected output

+-----------+----------------+--------------+
|   date    | challenge_name | updated_name |
+-----------+----------------+--------------+
| 1/11/2022 | find the bug   | find the bug |
| 1/12/2022 | challenge      | challenge 1  |
| 1/13/2022 | kill pill      | kill pill    |
| 1/14/2022 | hello copter   | hello copter |
| 1/15/2022 | challenge      | challenge 2  |
| 1/16/2022 | miami          | miami        |
| 1/17/2022 | hello world    | hello world  |
| 1/18/2022 | challenge      | challenge 3  |
| 1/19/2022 | dominc         | dominc       |
| 1/20/2022 | challenge      | challenge 4  |
+-----------+----------------+--------------+
Tags:

2

Answers


  1. 0

    This might work:

    -- Fuzzy match
CASE 
    WHEN REGEXP_LIKE(challenge_name, 'challenge') THEN 
        CONCAT('challenge ', 
               ROW_NUMBER() OVER (
                   PARTITION BY REGEXP_LIKE(challenge_name, 'challenge')
                   ORDER BY date
               )
        )
    ELSE challenge_name
END AS updated_name

    EDIT

    Added order by clause to the window function

    Login or Signup to reply.
  2. 0

    A simple case with rank should do the trick. Rank will assign a rank for each specific challenge_name group, I didn’t filter out only the values when challenge_name = challenge, because they are filtered on the case condition

    select `date`,
       challenge_name,
       case  when challenge_name = 'challenge' then concat('challenge ', rank() over(partition by challenge_name order by `date` asc )) 
             else challenge_name end  as challenge_name                               
  from test
  order by `date` asc ;

    https://dbfiddle.uk/oRJ0bQh7

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search