how do I make a row_number keeping the document number with different plots?

Question

I would like to know how to create a row_number keeping the numbers of documents equal creating the sequence by parcel. example:

doc / cliente / parcela
10     15         1
10     15         2
10     15         3
23     59         1
23     59         2
23     59         3

my sequence has to stay only 1 and 2 because they are documents of equal numbers differentiating only the plots. the way I’m doing here row_number ta creating the sequence with each doc getting 1, 2, 3, 4, 5, 6 and this way is not the right one.

Answer 1

See if in my example below meets your need:

create table #temp (doc int,cliente int)

insert into #temp
values
 ('10','15')
,('10','15')
,('10','15')
,('23','59')
,('23','59')
,('23','59')

select *
    , ROW_NUMBER () OVER (PARTITION BY doc ORDER BY doc ASC) as Parcela
    , ROW_NUMBER() OVER (ORDER BY doc) AS 'Row Number'
    , RANK() OVER ( ORDER BY doc ) AS 'Rank'
    , DENSE_RANK() OVER ( ORDER BY doc ) AS 'Dense Rank'
    , NTILE(4) OVER ( ORDER BY doc ) AS 'Quartile'
  from #temp

Upshot:

doc |cliente|Parcela |Row Number    |Rank   |Dense Rank |Quartile
10  |15     |1       |1             |1      |1          |1
10  |15     |2       |2             |1      |1          |1
10  |15     |3       |3             |1      |1          |1
23  |59     |1       |4             |4      |2          |2
23  |59     |2       |5             |4      |2          |2
23  |59     |3       |6             |4      |2          |2

A brief explanation about Ranking functions:

ROW_NUMBER () OVER ([ <partition_by_clause>] <order_by_clause>)

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

RANK () OVER ([ <partition_by_clause>] <order_by_clause>) ([RANK () OVER <partition_by_clause>] <order_by_clause>)

Returns (Rank) the position of each row within the partition of a result set.

DENSE_RANK () OVER ([ <partition_by_clause>] <order_by_clause>) ([DENSE_RANK () OVER <partition_by_clause>] <order_by_clause>)

Returns (Rank) the position of rows within the partition of a result set without any gaps in the ranking.

NTILE (integer_expression) OVER ([ <partition_by_clause>] <order_by_clause>)

Distributes the rows of an ordered partition into a certain number of groups.

Answer 2

Maybe then it’ll work out:

WITH DocumentoLinha AS
(
     SELECT RC1_DOCREC, 
            ROW_NUMBER() OVER (ORDER BY RC1_DOCREC) AS NumeroLinha
       FROM RECEBER1 
   GROUP BY RC1_DOCREC
)
SELECT R2.RC2_DOCREC,
       R1.RC1_CLIENTE, 
       R2.RC2_PARCELA,
       DL.NumeroLinha
--INTO MIG_DOCREC
  FROM MIG_CARAJAS.. RECEBER1 AS R1 INNER JOIN
       MIG_CARAJAS.. RECEBER2 AS R2 ON R1.RC1_DOCREC = R2.RC2_DOCREC INNER JOIN
       DocumentoLinha AS DL ON R1.RC1_DOCREC = DL.RC1_DOCREC
 WHERE RC2_DOCREC IN ('12-G','50-G')

Answer 3

my select:

SELECT RC2_DOCREC,
ROW_NUMBER () OVER (PARTITION BY R2.RC2_DOCREC ORDER BY R2.RC2_DOCREC) NUMDOCUMENTO,
RC1_CLIENTE , RC2_PARCELA
 --INTO MIG_DOCREC
 FROM MIG_CARAJAS.. RECEBER1 R1
INNER JOIN MIG_CARAJAS.. RECEBER2 R2 ON R1.RC1_DOCREC=R2.RC2_DOCREC
WHERE RC2_DOCREC IN ('12-G','50-G')

what has returned: Obs: row_number is the second column

12-G            1   12  1
12-G            2   12  2
12-G            3   12  3
50-G            1   12  1
50-G            2   12  2
50-G            3   12  3
50-G            4   12  4
50-G            5   12  5
50-G            6   12  6
50-G            7   12  7
50-G            8   12  8
50-G            9   12  9
50-G            10  12  10

Client table portions are already formatted correctly. I want to create a document sequence. This way you passed me he is creating a sequential as of parcels. The result I want him to bring is just document 1 and 2 for the 12-G and 50-G documents