WINDOW function alternative in HAVING Clause

Question

I'm trying to get the oldest processing date on each product like this:

select prod_id, prod_name, prod_date, min(prod_date) over (partition by prod_id) as min_prod_date
from dim_product
where prod_name in ('xxx', 'yyy', 'zzz')
having prod_date = min_prod_date

This gives me the error:

ORA-00904: "min_prod_date": invalid identifier

So I tried to use the min() function in the having clause which gave me the error:

ORA-30483: window functions are not allowed here

As an alternative I modified my query like this:

with temp as(
select prod_id, prod_name, prod_date, min(prod_date) over (partition by prod_id) as min_prod_date
    from dim_product
    where prod_name in ('xxx', 'yyy', 'zzz')
    having prod_date = min_prod_date))
select * from temp where prod_date = min_prod_date

Is this the right way to do things or Is there any other efficient method?

Your alternative isn't quite equivalent to what you seemed to want to do in the original query. However, I guess the "alternative" is closer to what you need. Namely: Do you need the `min_prod_date` for each `prod_id` (one row for each distinct `prod_id`)? — , Feb 19 '20 at 03:12

score 2 · Accepted Answer · answered Feb 18 '20 at 22:36

One method uses rank() in a subquery:

select *
from (
    select 
        prod_id, 
        prod_name, 
        prod_date, 
        rank() over(partition by prod_id order by prod_date) rn
    from dim_product
    where prod_name in ('xxx', 'yyy', 'zzz')
) t
where rn = 1

As an alternative, you may also want to consider filtering with a correlated subquery:

select prod_id, prod_id, prod_date
from dim_product d
where d.prod_name in ('xxx', 'yyy', 'zzz') and d.prod_date = (
    select min(d1.prod_date)
    from dim_product d1
    where d1.prod_id = d.prod_id and d1.prod_name in ('xxx', 'yyy', 'zzz')
)

For efficiency in the latter query, you want an index on (prod_name, prod_id, prod_date).

score 1 · Answer 2 · answered Feb 18 '20 at 23:36

1

This sounds like aggregation:

select prod_name, min(prod_date)
from dim_product
where prod_name in ('xxx', 'yyy', 'zzz')
group by prod_name;

answered Feb 18 '20 at 23:36

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 3 · answered Feb 19 '20 at 02:15

You can use your query as sub query and fetch the desired output as following:

Select prod_id, prod_name, prod_date
From
(select prod_id, prod_name, prod_date, 
        min(prod_date) over (partition by prod_id) as min_prod_date
from dim_product
where prod_name in ('xxx', 'yyy', 'zzz'))
Where prod_date = min_prod_date

Cheers!!

WINDOW function alternative in HAVING Clause

3 Answers3