Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle Query

Posted on 2011-04-20
12
Medium Priority
?
320 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:anumoses
12 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35432210
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
 
LVL 12

Expert Comment

by:enachemc
ID: 35432232
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
 
LVL 6

Author Comment

by:anumoses
ID: 35432249
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35432258
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35432270
>>--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
 
LVL 6

Author Comment

by:anumoses
ID: 35432283
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35432305
>>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
 
LVL 6

Author Comment

by:anumoses
ID: 35432328
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 35432358
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 35432594
Thanks
0
 
LVL 32

Expert Comment

by:awking00
ID: 35432713
>> 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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35432917
>> 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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month11 days, 11 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question