What about 'nested exists', Rick?

where exists(
Select 1
From
catalog c
Where
c.col1 = :new_col1 And
c.col2 = :new_col2 And
...
c.colN = :new_coln and
not exists(
select * from
catalog c2
where
c2.catalog_id = c.catalog_id and /*whatever is required to find the
right item */
c2.pk <> c.pk and /*to avoid no matches*/
c2.new_date between c.new_date and :new_date)) /*This is the punch
line, it checks to see that there aren't any records between c.new_date
and :new_date */

I'm pretty certain that you only want to use the index for the id of the
catalog item in the innermost select. Unless you have very few items and
all of them have lots of records, this nested exists shouldn't be too
much slower than with only a simple exists clause.

HTH,
Set

Rick Debay wrote:

> I've found that vendors are sending us updates to items, where nothing
> changes from the data we already have except for the effective date of
> the change. Some of them are updating large portions of their product
> catalog this way, making me think they took the easy way out of sending
> us the date of the extract on every row, instead of the actual date the
> particular item in the catalog changed.
> I'd like to silently drop these. The problem is coming up with the
> following query (which will live in an exists conditional):
>
> /* see if there are any exact matches for any item who's date is
> immediately before or after ours */
> Select 1
> From
> catalog c
> Where
> c.col1 = :new_col1 And
> c.col2 = :new_col2 And
> ...
> c.colN = :new_coln and
> (:new_date >= date_previous_to_new_date and :new_date <=
> date_after_new_date)
>
> I could do the last part as something like (I know the max/min >< stuff
> isn't correct):
>
> :new_date >= select min(date_col) where pk_col=:new_pk_col and date_col
> < :new_date
>
> But that seems quite inelegant.