[PostgreSQL] window function: row_number() over (partition by col order by col)

Here has a query SQL statement and its result:

select p.identifier, i.value 
from project p, intent i
where identifier = 'qa_chat' and p.id = i.project_id
order by i.id desc
limit 10

We can find out the first column of the preceding picture has duplicate values (i.e. qa_chat), if I would like to leave blank when the value of first column is equal to preceding record:

We can use window function to fulfill this requirement, the SQL statement will look like:

with source as (
    select row_number() over(partition by p.identifier order by i.id desc) as row_number, 
           p.identifier, i.value 
    from project p, intent i
    where identifier = 'qa_chat' and p.id = i.project_id
    limit 10
)
select case when row_number = 1 then identifier else '' end as identifier, value
from source

Reference
[1] https://www.postgresql.org/docs/9.3/static/functions-window.html