Select only the first record of a column

Question

Select only the first record of a column

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

The select below returns something like

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 one record per product equal to the following

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

What do 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

mysql sql

asked by anonymous 21.08.2015 / 22:40

2 answers

Select like (2 different parameters) Best solution for Web service Rest [closed]

score 3 · Answer 1

You have missed the 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

score 1 · Answer 2

The A record appears 3x in your example. The only difference between them is Data.

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

Something like this:

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