Row Number Based on condition Help

Is someone can help me to write a code to add "RankID" column in my source file?

Sample Source Data
Source_id,sta_id ,Address1 ,City ,PaymentMethod,Invoice_Detail
21 ,KXV ,Rd , , ,
22 ,KXV ,PO Box234 ,Dallas, ,
31 , , , ,Cash ,;8555-4
42 ,Paid Sat , , , ,
Y ,180217 ,6 ,23550 , ,800-345-969
21 ,CNN ,TV , , ,
22 ,CNN ,PO Box 99 ,Chicag, ,
31 , , , ,Cash ,224;86799
42 ,Paid Sun , , , ,
Y ,180218 ,6 ,3456 , ,888-191-1900
Y ,180226 ,7 ,456 , ,800-900-8999

What I want after add/populate "RankID"

RankID,Source_id,sta_id ,Address1 ,City ,PaymentMethod,Invoice_Detail
1 ,21 ,KXV ,Rd , , ,
1 ,22 ,KXV ,PO Box234 ,Dallas, ,
1 ,31 , , , ,Cash ,;8555-4
1 ,42 ,Paid Sat , , , ,
1 ,Y ,180217 ,6 ,23550 , ,800-345-969
2 ,21 ,CNN ,TV , , ,
2 ,22 ,CNN ,PO Box 99 ,Chicag, ,
2 ,31 , , , ,Cash ,224;86799
2 ,42 ,Paid Sun , , , ,
2 ,Y ,180218 ,6 ,3456 , ,888-191-1900
2 ,Y ,180226 ,7 ,456 , ,800-900-8999

Thanks in advance.

Hi

SELECT RANK() OVER(partition by column ORDER by column) as RANKID ,
other columns

Please try this .. Let me know if it is what you are looking for !!

Thanks, harishgg1 for your reply. However, it is not working. Below is my SQL sample code for testing.
What I am trying to do to create batches from my source data. The batch start of "Source_ID = 22" until next Source_id = 22.

Please let me know if my question is not clear or have any questions. Thank You.

Blockquote

CREATE TABLE ##SourceTable
(
source_id VARCHAR(20)
,sta_id VARCHAR(20)
,Address1 VARCHAR(50)
,City VARCHAR(50)
,PaymentMethod VARCHAR(50)
,Invoice_Detail VARCHAR(50)
)

INSERT INTO ##SourceTable
SELECT '21','KXV','Rd',NULL,NULL,NULL
UNION
SELECT '22','KXV','PO Box234','Dallas',NULL,NULL
UNION
SELECT '31',NULL,NULL,NULL,'Cash',';8555-4'
UNION
SELECT '42','Paid Sat',NULL,NULL,NULL,NULL
UNION
SELECT 'Y','180217','6','23550',NULL,'800-345-969'

INSERT INTO ##SourceTable
SELECT '21','CNN','TV',NULL,NULL,NULL
UNION
SELECT '22','CNN','PO Box 99','Chicago',NULL,NULL
UNION
SELECT '31',NULL,NULL,NULL,'Cash','224;86799'
UNION
SELECT '42','Paid Sun',NULL,NULL,NULL,NULL
UNION
SELECT 'Y','180218','6','3456',NULL,'888-191-1900'
UNION
SELECT 'Y','180226','7','456',NULL,'800-900-8999'

SELECT * FROM ##SourceTable

Hi

I am having computer issues

Dont know when it will be resolved

Hopefully someone else can help you !!!
Sorry

Do you have an identity column or some other sequence value in the row? Physical order in rows is NOT guaranteed, so you're taking a big chance trying to assign rank just based on relative row position after a '21' row.

hi

i know its been a long long time ..don't know if you still remember

I think i know what you are looking for
Example
data
1
a
b
e
1
c
a

i think you want to group like this in BATCHES
++++++++++++++
1
a
b
e
1
+++++++++++++++
a
b
e
1
c
a
++++++++++++

this is what I am thinking
so based on that

I have come CLOSE to the final step
maybe some experts watching can help
:slight_smile:
:slight_smile:

SQL .. Still some work left
;WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS rn, 
                a.source_id 
         FROM   (SELECT DISTINCT source_id 
                 FROM   sourcetable) a), 
     cteall 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS rn, 
                * 
         FROM   sourcetable), 
     cteok 
     AS (SELECT b.rn, 
                a.source_id, 
                Min(a.rn) AS minrn, 
                Max(a.rn) maxrn 
         FROM   cteall a 
                JOIN cte b 
                  ON b.source_id = a.source_id 
         GROUP  BY b.rn, 
                   a.source_id),
    cteFinal
    AS  (
    select a.rn as rnok,b.* from cteok  a join cteall b on a.source_id = b.source_id 
    and b.rn between a.minrn and a.maxrn
    )
    select * from cteFinal order by rn 
    go
Result

I think you need better sample data and take into consideration @ScottPletcher's advice, but this solution seems to work with the sample data provided:

CREATE TABLE #SourceTable
(
source_id VARCHAR(20)
,sta_id VARCHAR(20)
,Address1 VARCHAR(50)
,City VARCHAR(50)
,PaymentMethod VARCHAR(50)
,Invoice_Detail VARCHAR(50)
)

INSERT INTO #SourceTable
SELECT '21','KXV','Rd',NULL,NULL,NULL
UNION
SELECT '22','KXV','PO Box234','Dallas',NULL,NULL
UNION
SELECT '31',NULL,NULL,NULL,'Cash',';8555-4'
UNION
SELECT '42','Paid Sat',NULL,NULL,NULL,NULL
UNION
SELECT 'Y','180217','6','23550',NULL,'800-345-969'

INSERT INTO #SourceTable
SELECT '21','CNN','TV',NULL,NULL,NULL
UNION
SELECT '22','CNN','PO Box 99','Chicago',NULL,NULL
UNION
SELECT '31',NULL,NULL,NULL,'Cash','224;86799'
UNION
SELECT '42','Paid Sun',NULL,NULL,NULL,NULL
UNION
SELECT 'Y','180218','6','3456',NULL,'888-191-1900'
UNION
SELECT 'Y','180226','7','456',NULL,'800-900-8999'

;with cte
as
(

SELECT 
ROW_NUMBER() over(order by (select null))  x
,* 

FROM #SourceTable a
)

,cte2
as
(
select a.* 
from cte a
where a.source_id = '22'
)

select 
DENSE_RANK() over(order by b.rnk) RankID
,a.source_id, a.sta_id , a.Address1 , a.City , a.PaymentMethod , a.Invoice_Detail 
from cte a
outer apply
	(
	select isnull(Max(b.x),(select MIN(c.x) from cte2 c) ) rnk
	from cte2 b
	where b.x <= a.x 
	) b
 
go

drop table #SourceTable

hi

based on what you said

I understood like this

data if its like this
1
a
d
e
1
2
4
a

you want like this
batches
1 start to 1 end
grouped
1 1
a 1
d 1
e 1
1 1

a start a end
grouped
1
a 2
d 2
e 2
1 2
2 2
4 2
a 2

so i came up with this solution
please let me know if ok or not
:slight_smile:
:slight_smile:

drop create data
  USE tempdb 

go 

DROP TABLE sourcetable 

go 

CREATE TABLE sourcetable 
  ( 
     source_id      VARCHAR(20), 
     sta_id         VARCHAR(20), 
     address1       VARCHAR(50), 
     city           VARCHAR(50), 
     paymentmethod  VARCHAR(50), 
     invoice_detail VARCHAR(50) 
  ) 

INSERT INTO sourcetable 
SELECT '21', 
       'KXV', 
       'Rd', 
       NULL, 
       NULL, 
       NULL 
UNION 
SELECT '22', 
       'KXV', 
       'PO Box234', 
       'Dallas', 
       NULL, 
       NULL 
UNION 
SELECT '31', 
       NULL, 
       NULL, 
       NULL, 
       'Cash', 
       ';8555-4' 
UNION 
SELECT '42', 
       'Paid Sat', 
       NULL, 
       NULL, 
       NULL, 
       NULL 
UNION 
SELECT 'Y', 
       '180217', 
       '6', 
       '23550', 
       NULL, 
       '800-345-969' 

INSERT INTO sourcetable 
SELECT '21', 
       'CNN', 
       'TV', 
       NULL, 
       NULL, 
       NULL 
UNION 
SELECT '22', 
       'CNN', 
       'PO Box 99', 
       'Chicago', 
       NULL, 
       NULL 
UNION 
SELECT '31', 
       NULL, 
       NULL, 
       NULL, 
       'Cash', 
       '224;86799' 
UNION 
SELECT '42', 
       'Paid Sun', 
       NULL, 
       NULL, 
       NULL, 
       NULL 
UNION 
SELECT 'Y', 
       '180218', 
       '6', 
       '3456', 
       NULL, 
       '888-191-1900' 
UNION 
SELECT 'Y', 
       '180226', 
       '7', 
       '456', 
       NULL, 
       '800-900-8999' 

SELECT * 
FROM   sourcetable 

go
SQL
USE tempdb 

go 

; 
WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS grp, 
                a.source_id, 
                Min(rn)                     AS min1, 
                Max(rn)                     AS max1 
         FROM   (SELECT Row_number() 
                          OVER( 
                            ORDER BY (SELECT NULL)) AS rn, 
                        * 
                 FROM   sourcetable) a 
         GROUP  BY a.source_id) 
SELECT a.grp, 
       b.* 
FROM   cte a 
       JOIN (SELECT Row_number() 
                      OVER( 
                        ORDER BY (SELECT NULL)) AS rn, 
                    * 
             FROM   sourcetable) b 
         ON b.rn BETWEEN a.min1 AND a.max1
Result

drop table #SourceTable
CREATE TABLE #SourceTable
(
ID int identity(1,1),
source_id VARCHAR(20)
,sta_id VARCHAR(20)
,Address1 VARCHAR(50)
,City VARCHAR(50)
,PaymentMethod VARCHAR(50)
,Invoice_Detail VARCHAR(50)
)

INSERT INTO #SourceTable
SELECT '21','KXV','Rd',NULL,NULL,NULL
UNION
SELECT '22','KXV','PO Box234','Dallas',NULL,NULL
UNION
SELECT '31',NULL,NULL,NULL,'Cash',';8555-4'
UNION
SELECT '42','Paid Sat',NULL,NULL,NULL,NULL
UNION
SELECT 'Y','180217','6','23550',NULL,'800-345-969'

INSERT INTO #SourceTable
SELECT '21','CNN','TV',NULL,NULL,NULL
UNION
SELECT '22','CNN','PO Box 99','Chicago',NULL,NULL
UNION
SELECT '31',NULL,NULL,NULL,'Cash','224;86799'
UNION
SELECT '42','Paid Sun',NULL,NULL,NULL,NULL
UNION
SELECT 'Y','180218','6','3456',NULL,'888-191-1900'
UNION
SELECT 'Y','180226','7','456',NULL,'800-900-8999'
declare @MaxID int
select @MaxID = (select max(ID) from #SourceTable)

;with cte as (select ID,source_id,sta_id
from #SourceTable
where source_id='21'),
cte1 as (select row_number() over(order by (select null)) as rankk,c.source_id, c.ID, IsNull(lead(c.ID,1) over (order by c.ID)-1,@MaxID) ID2
from cte c)

select t.ID,t.source_id,t.sta_id,t.Address1,t.City,t.PaymentMethod,t.Invoice_Detail,c.rankk
from #SourceTable t
join cte1 c
on t.ID between c.ID and c.ID2