is there a way to use qualify in postgresql

Question

the below query works in teradata, is there a way to write the same query in postgresql? I am getting error while running this ppostgresql 10

select *
from
    product
qualify
    row_number() over (partition by product_key order by product_no) = 1; 

Answer 1

Postgres has distinct on, which should be even more performant:

select distinct on (product_key) p.*
from product p
order by product_key, product_no; 

This is usually the best method in Postgres to get one row per group.

Answer 2

You need subquery :

select p.*
from (select p.*,
             row_number() over (partition by product_key order by product_no) as seq
      from product p
     ) p 
where seq = 1;