SQLTeam.com | Weblogs | Forums

Row Number Based on condition Help


#1

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.


#2

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 !!


#3

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


#4

Hi

I am having computer issues

Dont know when it will be resolved

Hopefully someone else can help you !!!
Sorry


#5

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.


#6

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


#7

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

#8

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


#9

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