Reorder rank upon condition

What i like to do is to rank that when a customer address come more than once ( like 1390 des Rosiers) the first of the group should be a uneven number while respecting the order in NoSeqTrt ?


Hoping that my explication is clear.

Thanks in advance for your advice.

Cheers

Donne nous le data pas en image mais comme suivant

Create table sample

Insert into sample
Values

Here is the DDL

CREATE TABLE [dbo].[CUSTOM](
[Noseqtrt] [smallint] NULL,
[CUST_ADDRESS] nvarchar NULL,
[ADDRESS_COUNT] smallint NULL,
[RANK] [int] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (2,'133 Du Croissant-du-Bourg',1,1)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (2,'1440 des Roses',1,2)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (2,'1483 de Orchidée',1,3)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (2,'164 Du Croissant-du-Bourg',1,4)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (3,'1427 De Orchidée',1, 5)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (3,'1454 de Orchidee',1,6)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1328 de Orchidée',1,7)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1340 de Azalée',1,8)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1370 de Azalée',1,9)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1390 des Rosiers',1,10)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1390 des Rosiers',2,11)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1411 de Azalée',1,12)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'1411 de Azalée',2,13)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (4,'68 du Camélia',1,14)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (6,'1361 de Ange-Gardien',1,15)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (7,'7 Montmarquet',1,16)
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS,ADDRESS_COUNT,RANK)
VALUES (8,'487 de la Seugne',1,17)

hi

please see what i have done .. hope its what you want !!!

please click arrow to the left for drop create sample data
drop table custom 

CREATE TABLE [dbo].[CUSTOM](
[Noseqtrt] [smallint] NULL,
[CUST_ADDRESS] nvarchar(100) NULL,
) ON [PRIMARY]

INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (2,'133 Du Croissant-du-Bourg')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (2,'1440 des Roses')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (2,'1483 de Orchidée')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (2,'164 Du Croissant-du-Bourg')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (3,'1427 De Orchidée')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (3,'1454 de Orchidee')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (4,'1328 de Orchidée')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (4,'1340 de Azalée')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (4,'1370 de Azalée')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (4,'1390 des Rosiers')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (4,'1390 des Rosiers')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (4,'1411 de Azalée')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (4,'1411 de Azalée')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (4,'68 du Camélia')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (6,'1361 de Ange-Gardien')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (7,'7 Montmarquet')
INSERT INTO [dbo].[CUSTOM] (Noseqtrt,CUST_ADDRESS)
VALUES (8,'487 de la Seugne')
select 
        * 
	  , row_number() over(partition by [CUST_ADDRESS] order by [Noseqtrt]) as numberaddress 
	  , ROW_NUMBER() over(order by  [Noseqtrt] ) as Rank 
from CUSTOM 

image

image

When i see a address twice the first row should be a uneven number.

Thanks for your time

Cheers

I realize that i need to develop an algo to resolve that.

Thanks for the help

Cheers

hi we can discuss
.. what your thoughts are through LIVE chatting

what do you mean by algo ???

this back and forth on Forum can go on = Many Many Times