Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that looks like this:

+---------------------------------------------+
| EVENT_ID | ITEM_ID | PERSON_ID | EVENT_DATE |
+---------------------------------------------+
| 123      | 1       | 2         | 21-AUG-03  |
| 124      | 1       | 3         | 22-AUG-03  |
| 125      | 1       | 45        | 23-AUG-03  |
| 126      | 1       | (null)    | 24-AUG-03  |
| 127      | 2       | 2         | 25-AUG-03  |
| 128      | 2       | 6         | 26-AUG-03  |
| 129      | 2       | 1         | 27-AUG-03  |
+---------------------------------------------+

So, I need to get the latest (MAX(event_date)), non-null person_id per item_id. We have come up with a fairly simple PL/SQL approach to this, but were trying to get the job done with straight SQL. Anyone have an idea?

Just as a side-note, the event_id will not always be sequential like this. We have two redundant DB servers.

Thanks ahead of time.

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

Try this

create table event_table (
    event_id int,
    item_id int,
    person_id int,
    event_date date
);

insert into event_table values (123, 1, 2,    to_date('21-aug-03','DD-MON-YY'));
insert into event_table values (124, 1, 3,    to_date('22-aug-03','DD-MON-YY')); 
insert into event_table values (125, 1, 45,   to_date('23-aug-03','DD-MON-YY'));  
insert into event_table values (126, 1, null, to_date('24-aug-03','DD-MON-YY'));  
insert into event_table values (127, 2, 2,    to_date('25-aug-03','DD-MON-YY'));  
insert into event_table values (128, 2, 6,    to_date('26-aug-03','DD-MON-YY'));  
insert into event_table values (129, 2, 1,    to_date('27-aug-03','DD-MON-YY'));  


select * from event_table;

select t1.item_id, t1.Person_id, t1.event_date
from event_table t1
join 
(select item_id , max(event_date) max_event_date 
   from event_table where Person_id is not null group by item_id ) t2 
on t1.item_id = t2.item_id and t1.event_date = t2.max_event_date;
share|improve this answer
    
Thank you for the setup ddl/dml. I modified it to be valid Oracle syntax and changed my answer to use your table name. –  Leigh Riffel Apr 25 '11 at 13:02
    
OK. Nalroff tagged it Oracle. –  bernd_k Apr 25 '11 at 13:28
    
This seems to give me what I'm wanting, so thank you. I will mark it correct if it indeed does after some more testing. Thanks. –  Andy Apr 25 '11 at 16:17
add comment

The following requires only one full table scan.

SELECT Item_ID, Person_ID, Event_Date FROM
(
   SELECT Event_ID, Person_ID, Item_ID, Event_Date
      , MAX(EVENT_DATE) OVER (PARTITION BY Item_ID) HighEventDate 
   FROM Event_Table WHERE Person_ID IS NOT NULL
)
WHERE Event_Date = HighEventDate;
share|improve this answer
    
And at first I was tempted to propose to migrate the question to SO –  bernd_k Apr 25 '11 at 12:28
    
wasn't really sure whether to put it here or SO myself ;) –  Andy Apr 25 '11 at 16:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.