Select only the first record of a column

Question

select below returns something of the type

005-A Produto A 21/AGOSTO
005-A Produto A 20/AGOSTO
005-A Produto A 21/MAIO
005-B Produto B 21/AGOSTO

and I would like a registration per product equal to the following

005-A Produto A 21/AGOSTO
005-B Produto B 21/AGOSTO

What I need to adapt the following select ?

select i.referencia, i.nome, nf.data_emissao
        from notas_fiscais_itens nfi 
        inner join notas_fiscais nf on nf.id = nfi.nota_fiscal_id 
        inner join itens i on i.id = nfi.item_id                
        order by i.referencia, nf.data_emissao desc

Thank you

Answer 1

Missed you use GROUP BY+ field.

select i.referencia, i.nome, nf.data_emissao
    from notas_fiscais_itens nfi 
    inner join notas_fiscais nf on nf.id = nfi.nota_fiscal_id 
    inner join itens i on i.id = nfi.item_id                
    GROUP BY i.referencia, nf.data_emissao desc

Answer 2

Record A appears 3x in your example. The only difference between them is Date.

For what you want, you must return the Product with the latest Date. Therefore, it is necessary to use a Group By applying the MAX aggregator function.

Something like that:

select i.referencia, i.nome, max(nf.data_emissao)
        from notas_fiscais_itens nfi 
        inner join notas_fiscais nf on nf.id = nfi.nota_fiscal_id 
        inner join itens i on i.id = nfi.item_id                
        order by i.referencia, nf.data_emissao desc
group by i.referencia, i.nome