How to handle to modify this query to produced desired result 1

[AlStl]

Need some help in modifying existing initial query based on following data set?

create table date_test (id number, job_Num varchar2(50), job_detail varchar2(50), release_date date);

insert into date_test (id, job_Num, job_detail, release_date) values (1, 'A11111', 'TEST1', to_date('06/06/2002', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (2, 'X88888', 'TEST2', to_date('07/07/2005', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (3, 'P99999', 'TEST3', to_date('08/09/1987', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (4, 'H88888', 'TEST4', to_date('07/03/1999', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (5, 'B44444', 'TEST5', to_date('08/15/2010', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (6, 'U22222', 'TEST6', to_date('09/18/2001', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (7, 'A11111', 'TEST7', to_date('06/06/2001', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (8, 'A11111', 'TEST8', to_date('06/06/2010', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (9, 'H88888', 'TEST9', to_date('07/03/2010', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (10, 'B44444', 'TEST10', to_date('08/15/1988', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (11, 'B44444', 'TEST11', to_date('08/15/1996', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (12, 'B44444', 'TEST12', to_date('08/15/2002', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (14, 'A11111', 'TEST13', to_date('07/07/1985', 'mm/dd/yyyy'));
insert into date_test (id, job_Num, job_detail, release_date) values (15, 'A11111', 'TEST14', NULL);
insert into date_test (id, job_Num, job_detail, release_date) values (8, 'A11111', 'TEST15', to_date('06/06/2001', 'mm/dd/yyyy'));

select *
from
(select id,
job_num,
job_detail,
release_date,
dense_rank() over (partition by job_num order by
release_date desc) rnk
from date_test
where release_date is not null)
where rnk <= 3

Results:


ID JOB_NUM JOB_DETAIL RELEASE_DATE RNK
---------- ---------- ---------- ------------ ----------
8 A11111 TEST8 06-JUN-10 1
1 A11111 TEST1 06-JUN-02 2
7 A11111 TEST7 06-JUN-01 3
8 A11111 TEST15 06-JUN-01 3
5 B44444 TEST5 15-AUG-10 1
12 B44444 TEST12 15-AUG-02 2
11 B44444 TEST11 15-AUG-96 3
9 H88888 TEST9 03-JUL-10 1
4 H88888 TEST4 03-JUL-99 2
3 P99999 TEST3 09-AUG-87 1
6 U22222 TEST6 18-SEP-01 1
2 X88888 TEST2 07-JUL-05 1

12 rows selected.


Dagon and few other folks have helped me on this. ID, JOB_NUM, JOB_DETAIL make up the primary key. I want to modify above query to handle data with same date in this case 06-Jun-01 in following manner:

If this situation is encountered only pick records with higher JOB_DETAIL i.e. TEST15 and exclude lower value of JOB_DETAIL field i.e. TEST7 from result. So ideal resulting query will produce:

ID JOB_NUM JOB_DETAIL RELEASE_DATE RNK
---------- ---------- ---------- ------------ ----------
8 A11111 TEST8 06-JUN-10 1
1 A11111 TEST1 06-JUN-02 2
8 A11111 TEST15 06-JUN-01 3
5 B44444 TEST5 15-AUG-10 1
12 B44444 TEST12 15-AUG-02 2
11 B44444 TEST11 15-AUG-96 3
9 H88888 TEST9 03-JUL-10 1
4 H88888 TEST4 03-JUL-99 2
3 P99999 TEST3 09-AUG-87 1
6 U22222 TEST6 18-SEP-01 1
2 X88888 TEST2 07-JUL-05 1

Can it be done?

Thx

Al

 

[SantaMufasa]

...only pick records with higher JOB_DETAIL i.e. TEST15 and exclude lower value of JOB_DETAIL field i.e. TEST7

TEST15 is not higher than TEST7..."1" is not higher than "7". If you modify the data so that "TEST7" appears as "TEST07", then you can make it work.

I must go to a meeting presently, but when I return, I can offer some code to make it work.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[AlStl]

Mufasa,

Good point. I can make 07 instead of just 7. Actually, most of these JOB_DETAIL have values like EVT1, EVT2 etc.

Above values were just for example sake.

thx

Al

 

[Dagon]

Since Santa hasn't had chance to get back to you yet, I'll offer this suggestion. Just add job_detail or some variant of it to the order by columns e.g.

select *
from
(select id,
        job_num,
        job_detail,
        release_date,
        dense_rank() over (partition by job_num order by
                            release_date desc, regexp_replace(job_detail, '[0-9]',''), lpad(regexp_replace(job_detail, '[A-Z]'), 5, '0')) rnk
 from date_test
 where release_date is not null)
where rnk <= 3

Here I've used regular expressions to split job_detail into its string portion followed by the number padded to 5 digits. But depending what your data is, you might want to do something else.


For Oracle-related work, contact me through Linked-In.

 

[AlStl]

Dagon,

thx again for your help. Here is the ultimate goal. I have to run this query against the data set and insert the resulting records into another table.

Let me start this one more time. Here is the original data set (Primary key: ID, JOB_NUM, JOB_DETAIL):

ID JOB_NUM JOB_DETAIL RELEASE_DATE
AB1 A11111 EVT1 06/06/2010
AB2 B11111 EVT1 07/03/2001
AB3 C11111 EVT1 08/01/2002
AB1 A11111 EVT2 06/06/2010
AB2 C11111 EVT1 08/09/2002
AB1 D11111 EVT1 05/05/2004
AB1 E11111 EVT1 03/03/2003
AB3 F11111 EVT1 09/01/2003
AB2 G11111 EVT1 09/09/2004
AB3 H11111 EVT1 08/08/2004
AB2 J11111 EVT1 09/09/2009
AB3 K11111 EVT1 09/09/2010
AB3 K11111 EVT2 09/06/2010


I modified your initial suggestion and used partition by ID. Following is the resulting query that produced 3 latest records per ID.

Select * from (select ID,job_num,job_detail,release_date,
Dense_rank() over (partition by ID order by release_date desc) rnk
From table_name where release_date IS NOT NULL)
where rnk <=3

Query Results:

ID JOB_NUM JOB_DETAIL RELEASE_DATE RNK
AB1 A11111 EVT1 06/06/2010 1
AB1 A11111 EVT2 06/06/2010 2
AB1 D11111 EVT1 05/05/2004 3
AB2 J11111 EVT1 09/09/2009 1
AB2 G11111 EVT1 09/09/2004 2
AB2 C11111 EVT1 08/09/2002 3
AB3 K11111 EVT1 09/09/2010 1
AB3 K11111 EVT2 09/06/2010 2
AB3 H11111 EVT1 08/08/2004 3


The goal is to insert above resulting records in another table. The issue I am facing is: the table I am trying to insert records into a table that has ID & JOB_NUM as primary key. So, you can imagine records 2 & 8 will not load. A business rule has been established to take care of this situation i.e. if ID & JOB_NUM is same for two records then only load record with JOB_NUM= EVT2. So, desired result should look like following and could be inserted into final table:


ID JOB_NUM JOB_DETAIL RELEASE_DATE RNK
AB1 A11111 EVT2 06/06/2010 1
AB1 D11111 EVT1 05/05/2004 2
AB2 J11111 EVT1 09/09/2009 1
AB2 G11111 EVT1 09/09/2004 2
AB2 C11111 EVT1 08/09/2002 3
AB3 K11111 EVT2 09/06/2010 1
AB3 H11111 EVT1 08/08/2004 3


I hope this helps and sorry for not detailing everything upfront.

Al

 

[Dagon]

I'm a bit lost on what the requirements are now. I can't quite match your results because I get different results if I run your first query against the data set you gave (because of the dense_rank, I get AB1/E11111 whereas you don't).

If it's just to have a unique id, job_Num with the highest date/event number, then something as simple as this might work:

Select * from (select ID,job_num,job_detail,release_date,
row_Number() over (partition by ID, JOB_nUM order by release_date desc, lpad(substr(job_detail,4), 5, '0') desc) rnk
From date_test where release_date IS NOT NULL)
where rnk =1
order by id, job_num, job_detail, release_date desc

If it's more complicated than that and the query I gave before is getting you part way there, then maybe you could just apply an extra outer query to enforce the uniqueness e.g.

select id, job_Num, job_detail, release_date
from
(select id, job_Num, job_detail, release_date, row_number() over (partition by id, job_num order by release_date desc, lpad(substr(job_detail,4), 5, '0') desc) rnk2
from
(Select * from (select ID,job_num,job_detail,release_date,
Dense_rank() over (partition by ID order by release_date desc) rnk
From date_test where release_date IS NOT NULL)
where rnk <=3))
where rnk2 = 1
order by id, job_num desc, job_detail, release_date desc

For Oracle-related work, contact me through Linked-In.

 

[AlStl]

Dagon,

Thanks a lot for all your help and suggestions. I really appreciate it!!

Your second solution worked with minor changes. Again, I apologize of making things muddy.

Lesson learned.... next time I will be precise.

Have a nice weekend!!

Al

 

[SantaMufasa]

Thanks, Dagon, for taking care of business...Just after I responded earlier, we received word of a death in the family for which I have been away in California for the past week.

You Da' Man !

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.