Bring value from a grouped record

Asked

Viewed 200 times

1

Dear colleagues I took a query that I thought was simple but it’s complicated!

I have a database where I have all my NF per commodity input product, I need to find the product value 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 date, so far so good, but to bring the value that is being 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

Thanks in advance!

sql oracle

  • tries to place the trunc at date: Where trunc(date) < to_date('2016-01-01', 'yyyy-dd-mm')

1 answer

0


After much pondering, this was the query 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 anyone knows a better query, please help us improve ;)

  • The idea is the same, but the bank and Oracle think it has no LIMIT.

  • Mmm.. I’ll try something like this here and send you if I can ;)

  • And also he would not accept me having the value in the columns and not having in the group by. The table has millions of fields I’m simplifying to make it easy to understand. but she keeps the note items, the key would be Numnote, product, and date, in case I would need the product value in the last note of the year.

  • 1

    @Davidd.Rocha I need to clarify before continuing. I believe that maybe group by is not the best solution for what you need. What you want is given a certain product, know when was the last purchase of it and the last value? Or for all products, know what was the last purchase date and the last value of it? And in relation to the value, is the value of the product or invoice?

  • For all products the ultimate value. I have no note value in this table it is opened by products. Ex: ;Note | product | value 123 1581 2.85 123 1582 3.30 123 1583 4.50 and there the products are repeating themselves in different notes with different values and dates, then I needed to know the value of the product in the last note that entered in 2015. vlws

  • My table example did not work! rs

  • And that’s right @Rubico gave it right! Thank you very much, I didn’t know I could do Join with a select =) vlw for learning!

Show 2 more comments

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.