How to extract the oldest value for a column from a set of rows instead of all the rows?

user_12059330 0
Viewed 83

I have a table in the following format

Name|Token|   Date  |
---------------------
John|7    |2010-4-30|
John|7    |2011-4-30|
John|9    |2011-5-30|
John|9    |2012-7-30|
John|9    |2015-1-30|
John|7    |2016-10-1|
John|9    |2016-11-3|
John|9    |2018-1-1 |
John|7    |2021-9-9 |

I am trying to copy data from this table to a new table and I need to find out the oldest date from the latest set of rows that have the token 9 for every person. Like in this case, I need to get 2016-11-3 as my output. Now for the cases where all token's are 9 I can easily use the group by clause and min(date) and get my result, but in cases like this if I use the group by clause and min(date) I would get results like john|9|2011-5-30| but it is not correct because the token 7 breaks the set on 2016-10-1.

Additional cases Case 1:

Name|Token|   Date  |
---------------------
John|7    |2010-4-30|
John|7    |2011-4-30|
John|9    |2011-5-30|
John|9    |2012-7-30|

Answer => 2011-5-30. because row no 3 and 4, both have token 9 and are right after each other hence the sequence

John|9    |2011-5-30|
John|9    |2012-7-30|

Case 2:

Name|Token|   Date  |
---------------------
John|7    |2010-4-30|
John|7    |2011-4-30|
John|9    |2011-5-30|
John|9    |2012-7-30|
John|9    |2015-1-30|
John|7    |2016-10-1|
John|9    |2016-11-3|
John|9    |2018-1-1 |
John|7    |2021-9-9 |
John|9    |2022-1-1 |

Answer -> 2022-1-1, because the latest set of rows which have 9 as token and are right after each other (in this case only 1 row) is

John|9    |2022-1-1 |

Case 3:

    Name|Token|   Date  |
    ---------------------
    John|9    |2010-4-30|
    John|9    |2011-4-30|
    John|9    |2011-5-30|
    John|9    |2012-7-30|
    John|9    |2015-1-30|
    John|9    |2016-10-1|
    John|9    |2016-11-3|
    John|9    |2018-1-1 |
    John|7    |2021-9-9 |

Answer ->2010-4-30, here the latest set of rows with token 9 which are in a sequence are

    John|9    |2010-4-30|
    John|9    |2011-4-30|
    John|9    |2011-5-30|
    John|9    |2012-7-30|
    John|9    |2015-1-30|
    John|9    |2016-10-1|
    John|9    |2016-11-3|
    John|9    |2018-1-1 |
mysql sql
1 Answers

This is a Gaps & Islands problem with a twist. You can do:

with
g as (
  select *,
    sum(inc) over(partition by name order by date) as grp
  from (
    select *,
      case when token <> lag(token) over(partition by name order by date) 
      then 1 else 0 end as inc
    from t
  ) x
)
select g.name, min(g.date) as min_date
from g
join (
  select name, max(grp) as max_grp from g where token = 9 group by name
) m on m.name = g.name and m.max_grp = g.grp
group by g.name

Result (with modified data for testing purposes):

 name   min_date   
 ------ ---------- 
 Alice  2019-03-01 
 John   2016-11-03

See running example at db<>fiddle.

user_64361910
Related