0

In an Oracle DB I have a table with content similar to this:

Market_Intro_Date  Change_Date  Author
-------------------------------------------
   01.06.2025      10.07.2020   Meyer     *
   01.01.2025      30.06.2020   Harrin
   01.01.2025      01.05.2020   Floyd
   01.01.2025      15.04.2020   Peterson  *
   01.12.2024      20.03.2020   George
   01.12.2024      10.03.2020   Smith
   01.12.2024      15.01.2020   George    *
   01.01.2025      15.12.2019   Lee
   01.01.2025      01.11.2019   Alfonso
   01.01.2025      10.10.2019   Peterson  *
   01.07.2025      30.09.2019   Smith     *
   01.07.2024      20.09.2019   Lee
   01.07.2024      10.09.2019   Meyer     
   01.07.2024      01.05.2019   Smith     *

I need an SQL query that will return the first occurrence of every change of the market introduction date (marked with * at the end) along with the change date and the author.

So the result of the query will be:

Market_Intro_Date  Change_Date  Author
-------------------------------------------
   01.06.2025      10.07.2020   Meyer     
   01.01.2025      15.04.2020   Peterson  
   01.12.2024      15.01.2020   George    
   01.01.2025      10.10.2019   Peterson  
   01.07.2024      01.05.2019   Smith

Thank you in advance

Improve this question
6
  • 1
    Why did you mark 2 rows with "01.01.2025" and Peterson? Looks like instead of 01.01.2025 15.04.2020 Peterson should be 01.07.2025 30.09.2019 Smith Commented Jul 11, 2020 at 6:56
  • It looks like both Peterson rows should be there; but should 01.07.2025/30.09.2019/Smith also be included (and be marked with an asterisk), or was that date supposed to be 2024? Commented Jul 11, 2020 at 9:39
  • @SayanMalakshinov: Of interest is not when every market introduction date was defined but when the changes happened. Commented Jul 11, 2020 at 22:30
  • @AlexPoole: That was a mistake. I corrected it. Thank you for the hint! Commented Jul 11, 2020 at 22:31
  • So Peterson shouldn't appear twice, but Smith should? That's what the first query in my answer does; but why is Peterson (only) against 15.04.2025 rather then 10.10.2024 in your output; which doesn't match the asterisks now incidentally. I'm even less sure what you actually want. Commented Jul 11, 2020 at 22:45

4 Answers 4

Reset to default
1

You can use aggregation, with the keep ... first extension:

select Market_Intro_Date,
  min(Change_Date) as Change_Date,
  min(Author) keep (dense_rank first order by Change_Date) as Author
from your_table
group by Market_Intro_Date

MARKET_INTRO_DATE | CHANGE_DATE | AUTHOR  
:---------------- | :---------- | :-------
01-JUL-24         | 01-MAY-19   | Smith   
01-DEC-24         | 15-JAN-20   | George  
01-JAN-25         | 10-OCT-19   | Peterson
01-JUN-25         | 10-JUL-20   | Meyer   
01-JUL-25         | 30-SEP-19   | Smith

Read more.

I missed that one of the dates is repeated, so it's a gaps-and-islands problem. Tabibitosan to the rescue:

select Market_Intro_Date,
  min(Change_Date) as Change_Date,
  min(Author) keep (dense_rank first order by Change_Date) as Author
from (
  select Market_Intro_Date, Change_Date, Author,
    row_number() over (partition by Market_Intro_Date order by Change_Date)
      - row_number() over (order by Change_Date) as grp
  from your_table
)
group by Market_Intro_Date, grp
order by Change_Date desc;

MARKET_INTRO_DATE | CHANGE_DATE | AUTHOR  
:---------------- | :---------- | :-------
01-JUN-25         | 10-JUL-20   | Meyer   
01-JAN-25         | 15-APR-20   | Peterson
01-DEC-24         | 15-JAN-20   | George  
01-JAN-25         | 10-OCT-19   | Peterson
01-JUL-25         | 30-SEP-19   | Smith   
01-JUL-24         | 01-MAY-19   | Smith

db<>fiddle

This has a second row for Smith too, but that looks correct; not sure if your sample data missed an asterisk or 01.07.2025 was a typo.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Updated after clarification

Match_recognize usually works faster than old start_of_group solution based on analytic functions:

select *
from your_tab
match_recognize (
   order by Change_Date
   measures
       first(Change_Date) as Change_Date,
       first(Market_Intro_Date) as Market_Intro_Date,
       first(Author     ) as Author     
   pattern (A B*)
   define
      b as Market_Intro_Date = prev(Market_Intro_Date)  and Change_Date>prev(Change_Date)
)
order by 1;

Full example with your test data:

with your_tab(Market_Intro_Date, Change_Date, Author) as (
   select to_date('01.06.2025','dd.mm.yyyy'), to_date('10.07.2020','dd.mm.yyyy'),   'Meyer   ' from dual union all
   select to_date('01.01.2025','dd.mm.yyyy'), to_date('30.06.2020','dd.mm.yyyy'),   'Harrin  ' from dual union all
   select to_date('01.01.2025','dd.mm.yyyy'), to_date('01.05.2020','dd.mm.yyyy'),   'Floyd   ' from dual union all
   select to_date('01.01.2025','dd.mm.yyyy'), to_date('15.04.2020','dd.mm.yyyy'),   'Peterson' from dual union all
   select to_date('01.12.2024','dd.mm.yyyy'), to_date('20.03.2020','dd.mm.yyyy'),   'George  ' from dual union all
   select to_date('01.12.2024','dd.mm.yyyy'), to_date('10.03.2020','dd.mm.yyyy'),   'Smith   ' from dual union all
   select to_date('01.12.2024','dd.mm.yyyy'), to_date('15.01.2020','dd.mm.yyyy'),   'George  ' from dual union all
   select to_date('01.01.2025','dd.mm.yyyy'), to_date('15.12.2019','dd.mm.yyyy'),   'Lee     ' from dual union all
   select to_date('01.01.2025','dd.mm.yyyy'), to_date('01.11.2019','dd.mm.yyyy'),   'Alfonso ' from dual union all
   select to_date('01.01.2025','dd.mm.yyyy'), to_date('10.10.2019','dd.mm.yyyy'),   'Peterson' from dual union all
   select to_date('01.07.2025','dd.mm.yyyy'), to_date('30.09.2019','dd.mm.yyyy'),   'Smith   ' from dual union all
   select to_date('01.07.2024','dd.mm.yyyy'), to_date('20.09.2019','dd.mm.yyyy'),   'Lee     ' from dual union all
   select to_date('01.07.2024','dd.mm.yyyy'), to_date('10.09.2019','dd.mm.yyyy'),   'Meyer   ' from dual union all
   select to_date('01.07.2024','dd.mm.yyyy'), to_date('01.05.2019','dd.mm.yyyy'),   'Smith   ' from dual
)
select *
from your_tab
match_recognize (
   order by Change_Date
   measures
       first(Change_Date) as Change_Date,
       first(Market_Intro_Date) as Market_Intro_Date,
       first(Author     ) as Author     
   pattern (A B*)
   define
      b as Market_Intro_Date = prev(Market_Intro_Date)  and Change_Date>prev(Change_Date)
)
order by 1;

CHANGE_DATE         MARKET_INTRO_DATE   AUTHOR
------------------- ------------------- --------
2019-05-01 00:00:00 2024-07-01 00:00:00 Smith
2019-09-30 00:00:00 2025-07-01 00:00:00 Smith
2019-10-10 00:00:00 2025-01-01 00:00:00 Peterson
2020-01-15 00:00:00 2024-12-01 00:00:00 George
2020-04-15 00:00:00 2025-01-01 00:00:00 Peterson
2020-07-10 00:00:00 2025-06-01 00:00:00 Meyer
Improve this answer

Comments

0

Something like this should work:

select market_intro_date, change_date, replace(author,'    *','') author
from table 
where author like '%*%';
Improve this answer

2 Comments

I think the asterisks have been added to the data in the question to highlight those rows, rather than actually being in the table. Not entirely clear though... But if they are there, the number or spaces varies.
I used the asterisk to mark the entries for the output. But besides that the solution is brilliant ;-)
0

You can use not exists as follows:

Select t.*
  From your_table t
 Where not exists (select 1 from your_table tt
                    Where t.Market_Intro_Date = tt.Market_Intro_Date
                      And tt.changed_date < t.changed_date)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.