Bring value from a grouped record

Navigation
1

Dear colleagues, I grabbed a query that I thought was simple but it's complicated!

I have a database where I have all my NF per product incoming goods, I need to find the value of the product in the last entry of the year.

I arrived easy on the date of the note of each product, I grouped by product and gave a max on the date, so far so good, but to bring the value that is the problem, if I add the value in group by it shows the products several times! 

SELECT produto, MAX(data) 
 FROM notas 
 WHERE data < '2016-01-01' 
 GROUP BY produto <--- precisava trazer o valor da ultima entrada nessa query

Thank you in advance!

    
asked by anonymous 17.03.2016 / 14:19

1 answer

0

After a long time, this was the query that I managed to get, I hope it helps you: 

SELECT p.produto, p.data, n.valor
FROM notas n
INNER JOIN (SELECT produto, MAX(data) as data
            FROM notas
            WHERE data < '2016-01-01' 
            GROUP BY produto
) p ON (p.produto = n.produto and p.data = n.data)
ORDER BY p.data DESC;

If someone knows of a better query, please help us improve;)

    
17.03.2016 / 14:29
Relative Path with virtual subdirectory JSF Converter Generating Exception