These below all relate to the same record in the same file....basically it is labelled 'UNK' until someone assigns a product number to it. in this case the number 12345678 was assigned by Paul on 01Jan. Each record before/after that is when someone changes something on that record.
What I want is to capture that record, the 1st time when it goes from UNK to a number... and capture the user name and date etc from that line.
I have tried min, least, and I'm not sure about rownum or where to put the string if I did.
Car_Id Product # user name date
111 unk john 20Dec
111 unk alan 25Dec
111 unk pete 30Dec
111 12345678 paul 01Jan
111 12345678 jim 10Jan
222 unk alan 25Dec
222 unk pete 30Dec
222 87654321 paul 02Jan
222 87654321 steve 05Jan
But in logical terms I want it to do this... give me the 1st record after UNK.
Please can I have the full string if possible.
Sounds like maybe the analytic function row_number() would be the best way to do this:
with sample_data as (select 111 car_id, 'unk' product#, 'john' user_name, to_date('20/12/2014 10:12:24', 'dd/mm/yyyy hh24:mi:ss') dt from dual union all
select 111 car_id, 'unk' product#, 'alan' user_name, to_date('21/12/2014 10:12:24', 'dd/mm/yyyy hh24:mi:ss') dt from dual union all
select 111 car_id, 'unk' product#, 'pete' user_name, to_date('22/12/2014 10:12:24', 'dd/mm/yyyy hh24:mi:ss') dt from dual union all
select 111 car_id, '12345678' product#, 'paul' user_name, to_date('23/12/2014 10:12:24', 'dd/mm/yyyy hh24:mi:ss') dt from dual union all
select 111 car_id, '12345678' product#, 'jim' user_name, to_date('24/12/2014 10:12:24', 'dd/mm/yyyy hh24:mi:ss') dt from dual union all
select 222 car_id, 'unk' product#, 'alan' user_name, to_date('25/12/2014 10:12:24', 'dd/mm/yyyy hh24:mi:ss') dt from dual union all
select 222 car_id, 'unk' product#, 'pete' user_name, to_date('26/12/2014 10:12:24', 'dd/mm/yyyy hh24:mi:ss') dt from dual union all
select 222 car_id, '87654321' product#, 'paul' user_name, to_date('27/12/2014 10:12:24', 'dd/mm/yyyy hh24:mi:ss') dt from dual union all
select 222 car_id, '87654321' product#, 'steve' user_name, to_date('28/12/2014 10:12:24', 'dd/mm/yyyy hh24:mi:ss') dt from dual)
select car_id,
product#,
user_name,
dt
from (select sd.*,
row_number() over (partition by car_id order by dt) rn
from sample_data sd
where product# != 'unk')
where rn = 1;
CAR_ID PRODUCT# USER_NAME DT
---------- -------- --------- ---------------------
111 12345678 paul 23/12/2014 10:12:24
222 87654321 paul 27/12/2014 10:12:24