SELECT GROUP BY SHOP

Asked

Viewed 39 times

1

Good morning!

I have 4 stores at 1,2,3,4 want to consult the last sale of a product of each store.bringing only the last 4 sales, but one of each store. someone can help me?

select top 4 io.CodLoja,io.NumOrc,MAX(datafechamento) 
from ItensOrcamento io 
  inner join Orcamento o on io.CodLoja = o.CodLoja and 
                            io.NumOrc = o.NumOrc 
where io.CodProd = 4446 
and o.Fechado = 2
group by io.CodLoja,io.NumOrc,o.DataFechamento 
order by o.DataFechamento desc
sql sql-server

  • Inform DBMS as this limit differs by Bank.

  • Sorry sql server

  • There’s no point in you putting o.DataFechamento in its GROUP BY clause if you are using this field in the aggregation function (MAX). If you want only per store for what reason you are including io.NumOrc in the query?

  • I need that information, the order number might be the same but in different stores. this select I put was the last one I tried, but I couldn’t yet. SHOP ORDER VALUE 1 10 50 2 10 55 3 15 60 4 20 48

1 answer

0


You use a WITH together with ROW_NUMBER to obtain the latest data:

WITH vendas AS (
  SELECT ROW_NUMBER OVER(PARTITION BY io.codloja ORDER BY o.datafechamento DESC) AS posicao
         io.CodLoja,
         io.NumOrc,
         o.datafechamento
  INNER JOIN Orcamento o
          ON io.CodLoja = o.CodLoja
         AND io.NumOrc = o.NumOrc
  WHERE io.CodProd = 4446 
    AND o.Fechado = 2
)
SELECT v.codloja,
       v.numorc,
       v.datafechamento
  FROM vendas v
 WHERE posicao = 1

ROW_NUMBER

Returns the sequential number of a row in a partition of a result set, starting at 1 for the first row of each partition.

  • 1

    You gave right thanks

Browser other questions tagged sql sql-server

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