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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have the following table:

Ineedtobringonlytheoldestsaleoftheclient,regardlessofthetypeofsale,howeverIneedthetypeofsaletocomeinselect,beforeIhadasimilarquestionandtheyhelpedmehereinStackOverflow,butraisingthelevelofstudies,Icameupwiththisproblem.

BeforeIhadthefollowingqueryanditworked,howeverIdidnothavethe"type" of the sale, now having the type of sale, it precludes the use of Group By , as I could do:

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

I need to get the following result:

sql sql-server

asked by anonymous 08.05.2018 / 15:40

2 answers

0

You can do this in several ways. The problem will be if you have more than one sale on 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 do not have sql installed, so I did head. to understand the command Rank is a sql function, in which fz ranks. (1,2,3) etc. partition by are the fields you want to single, type a key. order by is used to make the hacking. You can see that it does not have the type field in rank, because you do not have to. Hope it helps !!!

08.05.2018 / 15:57

VBA code to delete line 2 filters on BindingSource no vb

score 1 · Accepted Answer

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, it 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