OVER function and first_value

Question

I have a table named ARTICLE with many columns, specifically MFC and ANA. I want, for each MFC, the first ANA which is not null.

So, I've written this query:

select mfc, first_value(ana) over(partition by mfc) as FirstAna
from article
where ana is not null

But it returns many rows for each MFC. What is the solution?

Answer 1

As it seems that you don't care which value to return (no ORDER BY clause), why not

select mfc,
       max(ana)
from article
where ana is not null
group by mfc