How to select only the largest move in Sql?

Assuming the product ID is just gtin, you can use rank with Partition :

rank() OVER (PARTITION BY produto.gtin ORDER BY estoque.id desc)

  

ps. Sort by the number of the sale, will have problems with that.

F a select select by ranking the "sales" in descending order. That is, the last sale (stock.id is the sale right?) Will be 0. Then you select from that table all the records where rank is 0.

Example:

With tabela as (
SELECT 
    estoque.id, 
    produto.gtin, 
    produto.descricao, 
    estoque.saldo_atual, 
    estoque.loja, 
    rank() OVER (PARTITION BY produto.gtin ORDER BY estoque.id desc) as i
FROM produto 
INNER JOIN estoque ON (produto.id = estoque.id_produto_empresa)
WHERE estoque.loja = '16')

select * from tabela where i = 0;

  

ps. You're good here, if you can test and give feedback. If you can do SQLFiddle also helps.

Test this way:

SELECT produto.gtin, produto.descricao, 
  (SELECT max(estoque.id) 
    FROM estoque 
    WHERE estoque.id_produto_empresa = produto.id) AS estoque_id, 
  estoque.saldo_atual, estoque.loja 
FROM produto 
INNER JOIN estoque ON (produto.id = estoque.id_produto_empresa) 
WHERE estoque.loja = '16'
GROUP BY produto.descricao
ORDER BY produto.descricao ASC

If possible, put your structure in SQL Fiddle .

Make a subquery that returns all desired inventory IDs using max() , grouping by what they have in common, which I understood from your question is id_produto . You can even filter the store to generate a smaller recordset and streamline the query up there. For example:

select * from estoque 
where id in (select max(id) from estoque where loja = 16 group by id_produto);

Or, avoiding the use of in and making join for better performance:

select * from estoque e1 join
  (select max(id) as id from estoque where loja = 16 group by id_produto) e2 
  on e1.id = e2.id

This would result in a stock ID (the highest or the most recent) for each product in that store:

| id | id_produto | loja |
|----|------------|------|
|  4 |          2 |   16 |
|  5 |          3 |   16 |
|  8 |          1 |   16 |
|  9 |          4 |   16 |

Finally, just paste this filtered inventory into your query that makes join with the product table to get descriptions, etc. I suggest using CTE with with , which makes the whole thing much more readable:

with e as (
  select e1.id, e1.id_produto, e1.loja from estoque e1 join
    (select max(id) as id from estoque 
     where loja = 16 group by id_produto) e2
    on e1.id = e2.id
)
select
  e.id,
  p.gtin, 
  p.descricao,
  e.loja
from produto p join e on p.id = e.id_produto;

This would return something like:

| id | gtin | descricao | loja |
|----|------|-----------|------|
|  8 | 0001 | Produto a |   16 |
|  4 | 0002 | Produto b |   16 |
|  5 | 0003 | Produto c |   16 |
|  9 | 0004 | Produto d |   16 |

Follow the example in SQL Fiddle: link

In this fiddle you can see that I put together a simplified schema, but based on what you presented by your question. If there is something that does not fit in your schema, I suggest you post a sample fiddle of the data so we can run more reliable tests.