Select data which was changed once-CodePudding

Hi I am new in Oracle/PLSQL.

I have a question. I have a table which stores data like this:

Example:
Table 1                     
col1        col2     col3
1           true     2023-01-01  
1           true     2022-12-06    <- I need this row  
1           false    2022-11-03  
2           true     2018-05-01    <- I need this row 
2           false    2018-04-06    
2           false    2018-01-03

I want to select the first row which changed col2 to true. It means I need second and fourth rows from the above example. I have a lot of data in a table and I need query which will work for all of them. col1 is not unique, cot3 is a timestamp when it was changed.

How can I do that with a Query?

CodePudding user response：

row_number analytic function helps.

(Members with reputation high enough will see Rahul_Mandhane's post which uses that approach, but has been deleted by moderator; I don't know why - flagged it, hoping I'll find out the reason. That answer returned wrong result because of wrong order by clause, though).

[EDIT: got the answer; because of ChatGPT being temporarily banned]

Meanwhile:

Note that date values you posted are ambiguous; it is impossible to know what any of dates represents because days and months are valid in any format model (yyyy-mm-dd and yyyy-dd-mm), and you didn't explain which is which. For "2022-12-06", what is it? 6th of December, or 12th of June? Could be both. The same goes for all sample dates.

I suppose it is yyyy-mm-dd.

SQL> alter session set nls_date_Format = 'yyyy-mm-dd';

Session altered.

Sample data:

SQL> with test (col1, col2, col3) as
  2    (select 1, 'true' , date '2023-01-01' from dual union all
  3     select 1, 'true' , date '2022-12-06' from dual union all
  4     select 1, 'false', date '2022-11-03' from dual union all
  5     select 2, 'true' , date '2018-05-01' from dual union all
  6     select 2, 'false', date '2018-04-06' from dual union all
  7     select 2, 'false', date '2018-01-03' from dual
  8    )

Query:

  9  select col1, col2, col3
 10  from (select t.*,
 11          row_number() over (partition by t.col1 order by t.col3) rn
 12        from test t
 13        where t.col2 = 'true'
 14       )
 15  where rn = 1;

      COL1 COL2  COL3
---------- ----- ----------
         1 true  2022-12-06
         2 true  2018-05-01

SQL>