help with a query

[mikedaruke]

How would I get this info?

Name, group, date
bob, 123, 10/1/07
bob, 678, 10/23/07
bob, 456, 9/1/06
sal, 123, 10/5/07
sal, 678, 10/8/07

I want to do list all names belonging to either group 123 or 678 but only if both there dates is older then 10/20/07

So bob wouldn't be listed, because he is satisfies the requirment in group 678.

But Sal would be listed because group 123 and 678 are older then 10/20/07 so I want to see him listed with the 3 columns, so

sal, 123, 10/5/07
sal, 678, 10/8/07

Any idea?

 

[Welshbird]

Can you give us a 'describe' on your table?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

select name, groupid, entrydate from 
(select name, groupid, entrydate,
max(entrydate) over (partition by name) as maxdate,
min(entrydate) over (partition by name) as mindate 
from mytable where groupid in (123,678))
where maxdate < to_date('10/20/07', 'MM/DD/YY') and mindate < to_date('10/20/07', 'MM/DD/YY')