records with most-recent-dates - Oracle Query help 2

[AlStl]

Guys,

Need some help with SQL.

I would like to get 3 records with most-recent-dates. Here are possible records

ID JOB_NUM JOB_DETAIL Release_date
1 A11111 TEST1 06/06/2002
2 X88888 TEST2 07/07/2005
3 P99999 TEST3 08/09/1987
4 H88888 TEST4 07/03/1999
5 B44444 TEST5 08/15/2010
6 U22222 TEST6 09/18/2001


First 3 fields make up the primary key i.e. ID,JOB_NUM, and JOB_DETAIL.

I want query only to return records 5, 2, and 1.

Thx

Al

 

[Turkbear]

Hi,
One way:
Select * from
(Select ID,JOB_NUM,JOB_DETAIL,Release_date
from table
order by Release_date DESC)
where rownum < 4;





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

An alternate method, in addition to Turkbear's excellent method, is via the use of Oracle's Analytic Functions:

select *
  from (select id,job_num,job_detail,release_date
              ,rank() over (order by release_date desc) rnk from AlStl)
 where rnk <= 3
/

 ID JOB_NUM            JOB_DETAIL      RELEASE_DATE           RNK
--- ------------------ --------------- --------------- ----------
  5 B44444             TEST5           15-AUG-10                1
  2 X88888             TEST2           07-JUL-05                2
  1 A11111             TEST1           06-JUN-02                3

3 rows selected.

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

 

[AlStl]

Turkbear,
Mufasa,

thanks for the reply ! A small omission. Lot of records in this table contain NULL in RELEASE_DATE field and for this reason its picking up those records, as it should. I want to eliminate those records from consideration i.e. where RELEASE_DATE is NULL. Something needs to be added to this select statement?

Al

 

[SantaMufasa]

Add the "WHERE RELEASE_DATE IS NULL" in front of the "FROM" clause of the inner query of either Turkbear's or my code.

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

 

[SantaMufasa]

'scuse me...I meant "WHERE RELEASE_DATE IS NOT NULL"

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,

I did that, but something is not correct. There is > 65 K rows in this table with 7 rows where RELEASE_DATE is NULL.

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

It returned 35 rows or so. should be lot more.

Here is a snap shot of record set:

ID JOB_NUM JOB_DETAIL Release_date
1 A11111 TEST1 06/06/2002
2 X88888 TEST2 07/07/2005
3 P99999 TEST3 08/09/1987
4 H88888 TEST4 07/03/1999
5 B44444 TEST5 08/15/2010
6 U22222 TEST6 09/18/2001
7 A11111 TEST7 06/06/2001
8 A11111 TEST8 06/06/2010
9 H88888 TEST9 07/03/2010
10 B44444 TEST10 08/15/1988
11 B44444 TEST11 08/15/1996
12 B44444 TEST12 08/15/2002
14 A11111 TEST13 07/07/1985
15 A11111 TEST14

I desire this to return:

8 A11111 TEST8 06/06/2010
1 A11111 TEST1 06/06/2002
7 A11111 TEST7 06/06/2001
5 B44444 TEST5 08/15/2010
12 B44444 TEST12 08/15/2002
11 B44444 TEST11 08/15/1996
2 X88888 TEST2 07/07/2005
3 P99999 TEST3 08/09/1987
9 H88888 TEST9 07/03/2010
4 H88888 TEST4 07/03/1999
6 U22222 TEST6 09/18/2001

Al

 

[SantaMufasa]

Al,

It appears that you might be changing specifications on us. I thought you said:

I would like to get 3 records with most-recent-dates.

But then you said:

It returned 35 rows or so. should be lot more.

If you want more than 3 records, how many do you want?



Could you please post the query you used to display the 35 rows? The queries that Turkbear and I posted could not possibly return "35 rows or so".

Also, when you say, "I desire this to return...", could you please confirm both the order that you want for the rows and the criteria for displaying the rows?

Thanks,

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,

First of all sorry for the confusion.

In my last post I just showed a snap shot of records in the table. There are little over 65 K records in the table. I did execute this query against all the records in the table i.e. little over 65K. Here is the query that yielded 35 records:

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

Based on primary key: id,job_num,job_detail I only want max number of records returned = 3 per primary key record set in order of JOB_NUM & release_date desc. For ex: as you can see in my last post: JOB_NUM - A11111 has 4 occurances, but in my desired snap shot you only see 3 in order of lastest to oldest release_date. JOB_NUM - H88888 has only two occurances and both show up in desired results. JOB_NUM - U22222 has only one occurance and it shows up in desired results. Record with release_date = NULL is dropped.


Thanks,

Al

 

[Dagon]

The information that you don't just want the highest three dates across the table is fairly crucial. It means you need an extra clause - a partition by clause.

Another thing you may want to think about is whether you want to include ties or not e.g. if A11111 has a record with date '06/06/2010', one with '06/06/2002' and two with '06/06/2001', do you want to show both the '06/06/2001' or only one?

Depending on your requirement, you could use either a DENSE_RANK or a ROWNUMBER. I've illustrated the difference below:

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

        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.

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

        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
         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

11 rows selected.

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

 

[AlStl]

Dagon,

This is awesome. A perfect solution. Also, as you pointed out another possible situation i.e. what if two JOB_NUM records have same release_date hence your first solution. For now second solution suits me better.

I need to examine data more carefully to see if there are any such cases where same release_date occurs twice for a JOB_NUM. In any case you covered both scenarios.

I really appreciate your feedback. Marking your, Mufasa, and Turkbear as valuable.

Al

 

[SantaMufasa]

I need to examine data more carefully to see if there are any such cases where same release_date occurs twice for a JOB_NUM.

Unless your application is doing a TRUNC(RELEASE_DATE), which trims off the time, then the only way that there would be duplicates is if someone enters the same RELEASE_DATE down to the second. (Of course, if the RELEASE_DATEs are being entered by hand, with no time component, then I presume that it is highly likely that there will duplicates.)


Let us know how things turn out.

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