Select only 1 record per month with an aggravating factor that prevents the use of Group By

Question

I have the following table:

I need to bring only the oldest customer sale, regardless of the type of sale, however I need the sale guy to come on select, before I had a similar doubt and helped me here in Stackoverflow, but increasing the level of studies, I arrived at this problem.

Before I had the following query and it was working, but I didn’t have the "kind" of sale, now having the kind of sale, it makes it impossible to use the Group By, how could I do:

SELECT 
    loja, 
    nome, 
    MONTH(data) 
    MIN(data) 
FROM vendas
GROUP BY loja, nome, MONTH(data)

I need to get the following result:

Answer 1

Here is a possible solution using ROW_NUMBER() or DENSE_RANK():

;WITH CTE AS 
(
   SELECT loja, 
          nome,
          tipo, 
          data,
          ROW_NUMBER() OVER (PARTITION BY nome, loja ORDER BY data ASC, tipo) RN    
     FROM vendas
)
SELECT loja, 
       nome,
       tipo, 
       data
       MONTH(data)
  FROM CTE
 WHERE RN = 1

Note that the previous query returns the oldest sale per customer, per store, if there are two sales with the same date, will return a "random record".

If you want to, for example, return all sales that occurred on this minimum date, you can use DENSE_RANK().

;WITH CTE AS 
(
   SELECT loja, 
          nome,
          tipo, 
          data,   
          DENSE_RANK() OVER (PARTITION BY nome, loja ORDER BY data ASC, tipo) DS
     FROM vendas
)
SELECT loja, 
       nome,
       tipo, 
       data
       MONTH(data)
  FROM CTE
 WHERE DS = 1

Answer 2

You can do this in many ways. The problem will be if you have more than one sale in the same day.

SELECT i.loja, 
    i.nome, 
    i.tipo, 
    i.data,
    RANK() OVER   
    (PARTITION BY i.loja, i.nome ORDER BY i.data DESC) AS Rank  
FROM vendas I  
WHERE rank=1;  

GO

I don’t have sql installed, so I did it in my head. for you to understand the command. Rank is a sql function, in which Fz ranks. (1,2,3) etc. Partition by are the fields you want unique, type a key. order by is used to make the racket. You can see that you don’t have the field type in rank, because you don’t need it. I hope it helps!!!