Forum Stats

  • 3,734,282 Users
  • 2,246,937 Discussions
  • 7,857,218 Comments

Discussions

Return concatenated value of same column where row contains last two modified column values

Mr.Peabody
Mr.Peabody Member Posts: 10 Red Ribbon
edited Apr 15, 2021 11:48AM in SQL & PL/SQL

I have last_modified (datetime) column and based on that, I want to have rows where row consist the last two modified concatenated values in the column.

It should contain all the last modified values combination.

Existing Values

Value     Last_modified_date
0           25-01-18
1           26-01-18
5           16-02-18
2           02-03-18
4           21-05-19
7           15-09-19

Required Format:

old_value (highphen) current_value
         0 - 1
         1 - 5
         5 - 2
         2 - 4
         4 - 7


Best Answers

  • cormaco
    cormaco Member Posts: 1,559 Bronze Crown
    Accepted Answer

    Here is one way:

    with example(value,last_modified_date) as (
        select 0,date '2018-01-25' from dual union all
        select 1,date '2018-01-26' from dual union all
        select 5,date '2018-02-16' from dual union all
        select 2,date '2018-03-02' from dual union all
        select 4,date '2019-05-21' from dual union all
        select 7,date '2019-09-15' from dual
    )
    select old_value || '-' || value value_change from (
        select lag(value) over (order by last_modified_date) old_value,value from example
    ) where old_value is not null;
    
    
    VALUE_CHANGE
    ------------
    0-1
    1-5
    5-2
    2-4
    4-7
    
    
    
    Mr.Peabody
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,398 Black Diamond
    Accepted Answer

    Match recognoze solution:

    with example(value,last_modified_date) as (
        select 0,date '2018-01-25' from dual union all
        select 1,date '2018-01-26' from dual union all
        select 5,date '2018-02-16' from dual union all
        select 2,date '2018-03-02' from dual union all
        select 4,date '2019-05-21' from dual union all
        select 7,date '2019-09-15' from dual
    )
    select  value_change
      from  example
      match_recognize(
                      order by last_modified_date
                      measures
                        first(value) || ' - ' || last(value) value_change
                      one row per match
                      after match skip to next row
                      pattern(pair{2})
                      define pair as 1 = 1
                     )
    /
    
    VALUE_CHANGE
    ------------
    0 - 1
    1 - 5
    5 - 2
    2 - 4
    4 - 7
    
    SQL>
    
    

    SY.

    Mr.Peabody

Answers

  • cormaco
    cormaco Member Posts: 1,559 Bronze Crown
    Accepted Answer

    Here is one way:

    with example(value,last_modified_date) as (
        select 0,date '2018-01-25' from dual union all
        select 1,date '2018-01-26' from dual union all
        select 5,date '2018-02-16' from dual union all
        select 2,date '2018-03-02' from dual union all
        select 4,date '2019-05-21' from dual union all
        select 7,date '2019-09-15' from dual
    )
    select old_value || '-' || value value_change from (
        select lag(value) over (order by last_modified_date) old_value,value from example
    ) where old_value is not null;
    
    
    VALUE_CHANGE
    ------------
    0-1
    1-5
    5-2
    2-4
    4-7
    
    
    
    Mr.Peabody
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,398 Black Diamond
    Accepted Answer

    Match recognoze solution:

    with example(value,last_modified_date) as (
        select 0,date '2018-01-25' from dual union all
        select 1,date '2018-01-26' from dual union all
        select 5,date '2018-02-16' from dual union all
        select 2,date '2018-03-02' from dual union all
        select 4,date '2019-05-21' from dual union all
        select 7,date '2019-09-15' from dual
    )
    select  value_change
      from  example
      match_recognize(
                      order by last_modified_date
                      measures
                        first(value) || ' - ' || last(value) value_change
                      one row per match
                      after match skip to next row
                      pattern(pair{2})
                      define pair as 1 = 1
                     )
    /
    
    VALUE_CHANGE
    ------------
    0 - 1
    1 - 5
    5 - 2
    2 - 4
    4 - 7
    
    SQL>
    
    

    SY.

    Mr.Peabody
Sign In or Register to comment.