Oracle Query

I have a query

 select donor_id,
           level_achieved,
          max(insert_date)insert_date
   from hbc_donor_gifts@pdon_new
   where donor_id = 'DN00345048'
  group by donor_id,level_achieved
---------------------------------
Sample data
DONOR_ID    LEVEL_ACHIEVED    INSERT_DATE
DN00345048       3            3/31/2011 5:00:18 AM
DN00345048      10           11/28/2010 5:00:12 AM
DN00345048       4            5/27/2010 5:00:30 AM
--------------------
What I want to achieve

DN00345048       3            3/31/2011 5:00:18 AM
-----------------
Only the level achieved for the latest insert date for every donor. I am sending query for one donor
LVL 6
anumosesAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I get 3.  not sure why you do not...
$rop table tab1 purge;
create table tab1 ( DONOR_ID varchar2(10),  DATE_LEVEL_ACHIEVED date, LEVEL_achieved number,    INSERT_DATE date );

insert into tab1 values('DN00000579',to_date('12/9/2010','MM/DD/YYYY'),15,to_date('12/14/2010 5:00:16 AM','MM/DD/YYYY HH:MI:SS AM'));
insert into tab1 values('DN00000579',to_date('2/17/2011','MM/DD/YYYY'),20,to_date('2/22/2011 5:00:17 AM','MM/DD/YYYY HH:MI:SS AM'));
insert into tab1 values('DN00000579',to_date('4/14/2011','MM/DD/YYYY'),3,to_date('4/19/2011 5:00:06 AM','MM/DD/YYYY HH:MI:SS AM'));
insert into tab1 values('DN00000579',to_date('11/12/2010','MM/DD/YYYY'),4,to_date('11/12/2010 9:38:24 AM','MM/DD/YYYY HH:MI:SS AM'));
insert into tab1 values('DN00000579',to_date('11/12/2010','MM/DD/YYYY'),3,to_date('11/12/2010 9:38:24 AM','MM/DD/YYYY HH:MI:SS AM'));
insert into tab1 values('DN00000579',to_date('11/12/2010','MM/DD/YYYY'),10,to_date('11/12/2010 9:38:24 AM','MM/DD/YYYY HH:MI:SS AM'));
commit;


select donor_id,
           level_achieved,
          insert_date
from
(
select donor_id,
           level_achieved,
          insert_date,
row_number() over(partition by donor_id order by insert_date desc) myrownum
   from tab1
)
where myrownum=1
/


SQL> select donor_id,
  2             level_achieved,
  3            insert_date
  4  from
  5  (
  6  select donor_id,
  7             level_achieved,
  8            insert_date,
  9  row_number() over(partition by donor_id order by insert_date desc) myrownum
 10     from tab1
 11  )
 12  where myrownum=1
 13  /
DN00000579              3 04/19/2011 05:00:06

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I thought we covered this already.

Use row_number() something like:

select donor_id,
           level_achieved,
          insert_date
from
(
select donor_id,
           level_achieved,
          insert_date,
row_number() over(order by insert_date desc) myrownum
   from hbc_donor_gifts@pdon_new
   where donor_id = 'DN00345048'
)
where myrownum=1
0
 
enachemcCommented:
select * from (
 select donor_id,
           level_achieved,
          nsert_date,
rank() over(partition by donor_id order by insert_date) rnk
   from hbc_donor_gifts@pdon_new
   where donor_id = 'DN00345048'
) t where t.rnk = 1

--rownum may not work always (joins)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
anumosesAuthor Commented:
The result from the above query is

DONOR_ID   LEVEL_ACHIEVED   INSERT_DATE
DN00345048         3                   3/31/2011 5:00:18 AM

But when I run the query for all donors I get like this
DONOR_ID           LEVEL_ACHIEVED           INSERT_DATE
DN00091996                   4  
----------------------------
Posting another sample data

DONOR_ID     LEVEL_ACHIEVED            INSERT_DATE
DN00000561            3                            8/2/2010 3:05:46 PM
DN00000561            4                            8/2/2010 3:09:53 PM
DN00000579            3                            4/19/2011 5:00:06 AM
DN00000579            4                            11/12/2010 9:38:24 AM
DN00000579           10                           11/12/2010 9:38:24 AM
DN00000579           15                           12/14/2010 5:00:16 AM
DN00000579           20                             2/22/2011 5:00:17 AM
---------------------
What I need is

DN00000561            4                            8/2/2010 3:09:53 PM
DN00000579            3                            4/19/2011 5:00:06 AM

0
 
slightwv (䄆 Netminder) Commented:
add partition by:

row_number() over(partition by order by insert_date desc) myrownum


I didn't add that in my original one because the original SQL you posted had the donor_id hard coded:  where donor_id = 'DN00345048'

You shouldn't change the requirements mid-question.
0
 
slightwv (䄆 Netminder) Commented:
>>--rownum may not work always (joins)

For my own education do you have an example where the row_number window function will differ from the rank window function?
0
 
anumosesAuthor Commented:
select donor_id,
           level_achieved,
          insert_date
from
(
select donor_id,
           level_achieved,
          insert_date,
row_number() over(partition by order by insert_date desc) myrownum
   from hbc_donor_gifts@pdon_new)
where myrownum=1

Error - missing expression
0
 
slightwv (䄆 Netminder) Commented:
>>partition by order by insert_date desc

what do you want to partition by?  donor_id.


(partition by donor_id order by insert_date desc)
0
 
anumosesAuthor Commented:
DONOR_ID    DATE_LEVEL_ACHIEVED   LEVEL    INSERT_DATE
DN00000579      12/9/2010                  15     12/14/2010 5:00:16 AM
DN00000579       2/17/2011                 20      2/22/2011 5:00:17 AM
DN00000579       4/14/2011                   3      4/19/2011 5:00:06 AM
DN00000579      11/12/2010                  4     11/12/2010 9:38:24 AM
DN00000579      11/12/2010                  3     11/12/2010 9:38:24 AM
DN00000579      11/12/2010                10     11/12/2010 9:38:24 AM
-------------Required data

DN00000579       4/14/2011                   3      4/19/2011 5:00:06 AM
0
 
anumosesAuthor Commented:
Thanks
0
 
awking00Commented:
>> For my own education do you have an example where the row_number window function will differ from the rank window function? <<

The only difference is that if the order by column is duplicated for the same partition, rank would return the same value for each row while row_number would return two different values. This brings up the question, how should it be handled if the donor_id has two insert_date values that are the same, even though the date_level_achieved and level values may be different?
0
 
slightwv (䄆 Netminder) Commented:
>> rank would return the same value for each row while row_number would return two different values

Thanks for that and you are correct.  I was just wanting to see how 'joins' affect them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.