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

sql update max(date) group by – Mysql

  • raw data
no group date value flag
1 a 2022-10-13 old y
2 a 2022-10-15 new y
3 b 2022-01-01 old n
4 b 2022-01-03 new n
  • step1. insert no1 raw
  • step2. modify date value using by no2 raw

and I want to update latest date no1 raw using by no2 raw
and the condition is where `flag` = "y"

  • final sql table
no group date value flag
1 a 2022-10-15 old y
3 b 2022-01-01 old n

is it possible?

+) I insert/update raw data line by line.

Tags:

2

Answers


  1. 0

    Not entirely clear but I hope below answer gives you a hint if not the solution.

    select  no,
       `group`,
        case when flag='Y' then mx_dt else `date` end as new_date,
        value,
        flag
from (    select no,
                 `group`,
                  value,
                  `date`,
                  flag ,
                  row_number() over(partition by `group` order by `date` asc ) as rn,
                  max(`date`) over (partition by `group`,(case when flag  <> 'Y' then `date`  end)   ) mx_dt
          from raw_data
    ) as tbl
where rn=1;

    Above code will select the max(date) per group if the flag=Y otherwise it will take the date per row.

    https://dbfiddle.uk/JhRUti2h

    Login or Signup to reply.
  2. 0

    The solution is to self join the source table and select the right field, prioritizing the latest date.

    Here you have a working query:

    WITH source_data AS (
SELECT 1 AS no_,  'a' AS group_, CAST('2022-10-13' AS DATE) AS date, 'old' AS value, 'y' AS flag
UNION ALL
SELECT 2, 'a', CAST('2022-10-15' AS DATE), 'new', 'y'
UNION ALL
SELECT 3, 'b', CAST('2022-01-01' AS DATE), 'old', 'n'
UNION ALL
SELECT 4, 'b', CAST('2022-01-03' AS DATE), 'new', 'n')

SELECT no_, group_, COALESCE(new_date, date), value, flag
FROM 
(SELECT * FROM source_data WHERE value = 'old') old_values
LEFT JOIN (SELECT group_ AS new_group, date AS new_date FROM source_data WHERE value = 'new' AND flag='y') new_values
ON old_values.group_ = new_values.new_group

    The result is what you expected:

    no_ group_  f0_ value   flag
1   a   2022-10-15  old y
3   b   2022-01-01  old n
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search