Return larger field to ID

Asked

Viewed 44 times

0

Good morning, I need some guidance regarding the following situation:

SQL

In this table I need to return only the MAX(nutab) for each codprod and control. I tried using the MAX, but so only returned what is equal to 80 that would be the max(nutab)... the problem is that I have more products in this table and for example the MAX(nutab) of the product 210 is 80, but the product 211 is already 76, this way I can not return all data only with MAX(nutab).

sql oracle

3 answers

1

From what I understand of your question you want to group the maximum values of NUTAB into CODPROD groups. For this SQL has GROUP BY, it groups the fields and if used together with MAX it can return what you want. I believe what you’re looking for is something like:

SELECT codprod, controle, vlrvenda, MAX(nutab) FROM suaTabela GROUP BY codprod, controle;

This way the values will be returned in three fields the CODPROD, the CONTROL and the other the largest NUTAB for these values of CODPROD and CONTROL.

More information about GROUP BY: https://www.w3schools.com/sql/sql_groupby.asp

GROUP BY in multiple columns : https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns

  • So even for sure, the problem is that I need to return the vlrvenda field in the query too... with this grouping no longer right

  • Good for this just add the vlrvenda between the selected columns. I will update the answer to see if it is what you are looking for.

1

Try this:

SELECT codprod, MAX(nutab)
FROM tabela
GROUP BY codprod

  • this way not certain, because then it will only return me the products that possess the MAX = 81, which are not all products... each product has a different MAX.

  • In case the product 210 has the MAX = 81, already the product 211 has the MAX = 76, if doing the above will only return the records with the MAX = 81

  • 1

    The code I’ve put in is grouping by codprod. It will catch the MAX(nutab) for each codprod. That’s not what you need?

  • I also don’t understand why it only returns max = 81. I believe this answer solves

0

I got it this way:

SELECT
   A.controle,
   A.codprod,
   A.vlrvenda
FROM
   TGFEXC A
LEFT JOIN
   TGFEXC B
   ON  A.codprod = B.codprod
   AND B.nutab > A.nutab
where 
B.nutab IS NULL

Thank you all!

Browser other questions tagged sql oracle

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