CODEWITHSUNDEEP
sqlsql-servert-sql
Ajit Goel
Ajit Goel

Reputation: 4388

Grouping records and starting Row Number as a odd number when partition changes

Please see the DDL below:

create table Test (RevObjId int, LegalPartyId int, IsPrimary int)

insert into Test (RevObjId, LegalPartyId, IsPrimary) values (10, 20, 0)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (10, 21, 0)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (10, 22, 1)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (11, 20, 1)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (11, 21, 0)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (12, 30, 1)
insert into Test (RevObjId, LegalPartyId, IsPrimary) values (13, 40, 0)

I am looking for the output below:

RevObjId LegalPartyId IsPrimary RowNumber
10 22 1 1
10 20 0 2
10 21 0 3
11 20 1 5
11 21 0 6
12 30 1 7
13 40 0 9

When I use the following query:

select RevObjId, 
       LegalPartyId, 
       IsPrimary, 
       row_number() over(partition by RevObjId order by RevObjId asc,IsPrimary desc,LegalPartyId asc) as RowNumber 
       from test;

I get the row numbers in sequence, each row incrememted by one, the row numbers get reset after the partition changes. How can I change the Row Number to the next odd number when partition(by RevObjId) changes? Here's my SQL Fiddle http://sqlfiddle.com/#!6/01d5c/22

We need the gaps between the row numbers because I need to generate the report in the following format. enter image description here

Here's a supporting question that I had asked: How to convert every other row to column in T-SQL?

Upvotes: 4

Views: 876

Answers (2)

Martin Smith
Martin Smith

Reputation: 453057

The need for this is highly suspect but I think this should do it.

The only time you get a gap is when the previous partition has an odd number of rows - and then the gap is 1 row. So this keeps track of the running count of previous partitions with odd numbers of rows and adds 1 to the row number total for each such partition.

WITH T
     AS (SELECT RevObjId,
                LegalPartyId,
                IsPrimary,
                odd_adj = CASE
                            WHEN RevObjId = LEAD(RevObjId) 
                                    OVER (ORDER BY RevObjId ASC, IsPrimary DESC, LegalPartyId ASC)
                              THEN 0
                            ELSE ROW_NUMBER() /*We are in the last row of the partition so can use rownumber as a more efficient alternative to count*/
                                    OVER (PARTITION BY RevObjId ORDER BY IsPrimary DESC, LegalPartyId ASC)%2
                          END,
                RowNumber = ROW_NUMBER() 
                                OVER(ORDER BY RevObjId ASC, IsPrimary DESC, LegalPartyId ASC)
         FROM   test)
SELECT RevObjId,
       LegalPartyId,
       IsPrimary,
       RowNumber
           +  SUM(odd_adj) OVER (ORDER BY RevObjId 
                                 ROWS UNBOUNDED PRECEDING) 
           - odd_adj AS RowNumber /*odd_adj is only potentially non zero for the last row in each partition 
                                   - if we are in the last row and it is 1 we need to deduct it 
                                     as this is not a previous partition */
FROM   T;

SQL Fiddle

Upvotes: 3

Radim Bača
Radim Bača

Reputation: 10701

Try it as follows

with groupcount as
(
  select RevObjId, (count(*) + 1)/ 2 * 2 as c
  from test
  group by RevObjId
), RevObjIdRN as
(
  select RevObjId, LegalPartyId, IsPrimary, 
         row_number() over (partition by RevObjId order by IsPrimary desc,LegalPartyId asc) as rn
  from test
)
select t2.RevObjId, t2.LegalPartyId, t2.IsPrimary, rn + relative
from (
  select RevObjId, sum(c) over (order by RevObjId) - c as relative
  from groupcount 
) t1  
join RevObjIdRN t2 on t1.RevObjId =  t2.RevObjId

sqlfiddle

The solution is based on the following part:

with groupcount as
(
  select RevObjId, (count(*) + 1)/ 2 * 2 c
  from test
  group by RevObjId
)
select RevObjId, sum(c) over (order by RevObjId) - c as relative
from groupcount

which returns the starting row_number for each group of RevObjId (the RevObjId offset). The rest is simply adding row_number() for each RevObjId to this offset.

Upvotes: 1

Related Questions