Oracle query to find the first or second row-CodePudding

I have a set of data with records of people and their terms. I am only concerned with terms 201030, 201040, & 201110. All others will be ignored. Some people will have several records, and some will only have one. I am writing a query to grab the first term for each person. If that 1st term is 201030, it should look at the next row. I need to return the term of the 2nd row if it is either 201040 or 201110, otherwise just return 201030. Return the 1st row term if it is either 201040 or 201110. This is a bit confusing so I tried to come with an image with the different scenarios. The green boxes indicate the record I should keep.

Here is the query I have so far. It runs but does not apply the case logic. It just returns the 1st term. How should I change this query?

select person_id, 
       min(term) as min_term,  -- including this field to verify the 1st term before changes
       case 
          when term = '201030' 
          then lead(min(term), 1, min(term)) over (order by min(term))
          else term
       end as term
from my_table 
group by person_id

CodePudding user response：

Here's one option. Read comments within code.

SQL> with test (person_id, term) as
  2    -- sample data
  3    (select 53303, 201030 from dual union all
  4     select 53303, 201040 from dual union all
  5     select 53303, 201110 from dual union all
  6     select 53303, 201140 from dual union all
  7     --
  8     select 14627, 201030 from dual union all
  9     select 14627, 201110 from dual union all
 10     select 14627, 201510 from dual union all
 11     --
 12     select 14702, 201030 from dual union all
 13     --
 14     select 28103, 201030 from dual union all
 15     select 28103, 201230 from dual union all
 16     select 28103, 201240 from dual union all
 17     select 28103, 201310 from dual union all
 18     --
 19     select 33634, 201040 from dual union all
 20     select 33634, 201110 from dual union all
 21     select 33634, 201130 from dual union all
 22     --
 23     select 32356, 201510 from dual
 24    ),

 25  rns as
 26    -- select next term and row numbers
 27    (select person_id,
 28       term,
 29       lead(term) over (partition by person_id order by term) next_term,
 30       row_number() over (partition by person_id order by term) rn
 31     from test
 32    )
 33  select person_id,
 34    max(case when rn = 1 and term = 201030 then
 35               case when next_term in (201040, 201110) then next_term
 36                    else term
 37               end
 38             when rn = 1 and term in (201040, 201110) then term
 39        end) result
 40  from rns
 41  group by person_id;

 PERSON_ID     RESULT
---------- ----------
     14702     201030
     32356
     33634     201040
     14627     201110
     28103     201030
     53303     201040

6 rows selected.

SQL>

CodePudding user response：

From Oracle 12, your can use MATCH_RECOGNIZE:

SELECT *
FROM   (
  SELECT *
  FROM   my_table
  WHERE  term IN (201030, 201040, 201110)
)
MATCH_RECOGNIZE (
  PARTITION BY person_id
  ORDER     BY term_order
  ALL ROWS PER MATCH
  PATTERN ( ^ {- IS30? -} ANY_ROW )
  DEFINE
    IS30 AS term = 201030
)

Which, for the sample data:

CREATE TABLE my_table (person_id, term, term_order) As
  SELECT 14627, 201030, 1 FROM DUAL UNION ALL -- Not this one
  SELECT 14627, 201110, 2 FROM DUAL UNION ALL -- This one
  SELECT 14627, 201510, 3 FROM DUAL UNION ALL -- Ignore this
  SELECT 14702, 201030, 1 FROM DUAL UNION ALL -- This one
  SELECT 28103, 201030, 1 FROM DUAL UNION ALL -- This one
  SELECT 28103, 201230, 2 FROM DUAL UNION ALL -- Ignore this
  SELECT 28103, 201240, 3 FROM DUAL UNION ALL -- Ignore this
  SELECT 28103, 201310, 4 FROM DUAL UNION ALL -- Ignore this
  SELECT 33634, 201040, 1 FROM DUAL UNION ALL -- This one
  SELECT 33634, 201110, 2 FROM DUAL UNION ALL -- Not this one
  SELECT 33634, 201130, 3 FROM DUAL UNION ALL -- Ignore this
  SELECT 32356, 201510, 1 FROM DUAL UNION ALL -- Ignore this
  SELECT 53303, 201030, 1 FROM DUAL UNION ALL -- Not this one
  SELECT 53303, 201040, 2 FROM DUAL UNION ALL -- This one.
  SELECT 53303, 201110, 3 FROM DUAL UNION ALL -- Not this one
  SELECT 53303, 201140, 4 FROM DUAL;          -- Ignore this

Note: In SQL, tables are unordered. If you want them to be in a specific order then you need to have something with which to apply that order (such as the task_order column) and to use an ORDER BY clause.

Outputs:

PERSON_ID TERM_ORDER TERM

14627 2 201110

14702 1 201030

28103 1 201030

33634 1 201040

53303 2 201040

PERSON_ID	TERM_ORDER	TERM
14627	2	201110
14702	1	201030
28103	1	201030
33634	1	201040
53303	2	201040

db<>fiddle here