question

danielefranzini-5701 avatar image
0 Votes"
danielefranzini-5701 asked danielefranzini-5701 commented

SQL SEQUENTIAL NUMBER WITH GROUP , THE OPPOSITE OF PARTITION BY

Hi,
I need to create a Progressive Number on different group in a Select on SQL, the number should be progressive with different group , but the same inside the same group.
Here is the code i use now with the result :


 select 
 ROW_NUMBER()OVER(PARTITION BY  SD.SALEDOCID ORDER BY sd.DOCNO ASC) AS ProgressivoDocumento,
 sd.SaleDocId ,
 cs.custsupp ,
 'CINV' AS TipoDocument,
 sd.DocNo As NumeroDocumento,
 sd.DocumentDate as DataEmissioneDocumento
 from MA_SaleDoc SD 
 JOIN MA_PyblsRcvbls PY on sd.DocNo = py.DocNo
 right join MA_CustSupp CS on cs.CustSupp = sd.CustSupp
 join MA_SaleDocSummary sds on sd.SaleDocId = sds.SaleDocId 
 join MA_SaleDocPymtSched sdp on sdp.SaleDocId = sd.SaleDocId
    
 where 
 ( sd.DocumentType = '3407874'or sd.DocumentType = '3407875') and py.Settled = 0  and sd.DocumentDate >='20210101'  and py.DocumentDate >='20210101' AND CS.CustSuppType = '3211264'

And this is what i find
144284-cattura.png

But i need exactly the opposite like this :
144301-cattura1.png


Can someone help me on how to solve this?
Thank you very much


sql-server-generalsql-server-transact-sql
cattura.png (191.3 KiB)
cattura1.png (110.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered danielefranzini-5701 commented

Try 'dense_rank() over (order by SaleDocId)'.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Really Thank you
It's work

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @danielefranzini-5701

Welcome to the microsoft TSQL Q&A forum!

The DENSE_RANK() function mentioned by Viorel-1 can solve your problem. Please refer to his answer. For more details, please refer to:
DENSE_RANK (Transact-SQL)

Regards
Echo

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.