How to select only the largest drive in Sql?

Question

I am in need of a help to search for the last move in the stock of each product. It happens that in the clause I made, he is returning me all sales related to that product, and I only need the last... Sales are ordered by id. I have drafted the following code:

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

This Query is returning me all the moves of each item, however I need that for each gtin, be displayed the largest ID.

Example:

id 766 gtin 004778 descricao AGITADOR BWC06A/BWG10A/WL09A/CWE06A/B/CWL08C-NF

id 2721 gtin 000672 descricao ALCOOL METILICO 500ML

I really appreciate anyone who can help me.

Answer 1

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

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

ps. Sort by sale number, you will have trouble with that.

Fselect by ranking "sales" downwards. That is, the last sale (stock.id is the right sale?) will be 0. Then you select from this table, all records where the 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. It’s running well here, if you can test and give feedback. If you can do Sqlfiddle it also helps.

Answer 2

Make a subquery that returns all desired stock Ids using max(), grouping by what they have in common, which from what I understood from your question is the id_produto. You can even already filter the store to generate a smaller recordset and streamline the query ahead. As 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 best 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 largest, or most recent) for every product in that store:

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

Finally, just stick this filtered stock in your query that makes join with the product table to pick up descriptions, etc. I suggest using CTE with with, that 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;

It would return to you 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 |

The following is an example in SQL Fiddle: http://sqlfiddle.com/#! 17/c67dd/4

In this fiddle you can see that I set up a simplified schema, but based on what you presented by your question. If there’s anything that doesn’t fit in your schema, I suggest you post a fiddle with a sample of the data so we can perform more reliable tests.

Answer 3

Test like this:

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, place your structure on SQL Fiddle.