Dense_rank() row number?

I'm trying to get a row number/rank for my data based on the change in Item and Bin with a reset every time the Item Number changes.

Here is my Script:

SELECT *
, ROW_NUMBER() over (partition by ITEMNMBR, DestBin, OrigQty Order by ITEMNMBR) as [Row Number]
--, DENSE_RANK() OVER (ORDER BY ITEMNMBR, LOCNCODE, DestBin) AS [Row Number]
FROM _DSI_NegativeInvetoryBins

Below is what the results are:

ITEMNMBR LOCNCODE DestBin Row Number
AA AS18ELF05SR WHSE5000 RCV1 1
AA AS18HP19RG WHSE5000 CAB Y1 1
AA AS18HP19RG WHSE5000 RCV1 1
AA AS18HP19RS WHSE5000 CAB Y1 1
AA AS18HP19RS WHSE5000 RCV1 1
BM 86027 WHSE5000 RCV1 1
BM 92233 OA WHSE5000 RCV1 1
BM BE93738 WHSE5000 1 1
BO E0KK-03 COBALT WHSE5000 1B08F1 1
BO E0KK-03 COBALT WHSE5000 CAB E9 1

What I really want is:

ITEMNMBR LOCNCODE DestBin Row Number
AA AS18ELF05SR WHSE5000 RCV1 1
AA AS18HP19RG WHSE5000 CAB Y1 1
AA AS18HP19RG WHSE5000 RCV1 2
AA AS18HP19RS WHSE5000 CAB Y1 1
AA AS18HP19RS WHSE5000 RCV1 2

I apologize for the formatting not sure how to copy/paste the results with formatting. Hopefully this is clear as to what I'm looking to accomplish.

hi

this is what i tried

looking at the results you want you put

not able to understand .. what you are looking for

:slight_smile:
:slight_smile:

Drop Create Data
use  tempdb 
go 


drop table data 
go 

create table data 
(
ITEMNMBR	 varchar(100) ,
LOCNCODE	 varchar(100) ,
DestBin	 varchar(100) ,
RowNumber  int 
)
go 


insert into data select 'AA AS18ELF05SR','WHSE5000','RCV1',	1
insert into data select 'AA AS18HP19RG','WHSE5000','CAB Y1',	1
insert into data select 'AA AS18HP19RG','WHSE5000','RCV1',	1
insert into data select 'AA AS18HP19RS','WHSE5000','CAB Y1',	1
insert into data select 'AA AS18HP19RS','WHSE5000','RCV1',	1
insert into data select 'BM 86027','WHSE5000','RCV1',	1
insert into data select 'BM 92233 OA','WHSE5000','RCV1',	1
insert into data select 'BM BE93738','WHSE5000',	1,1
insert into data select 'BO E0KK-03 COBALT','WHSE5000','1B08F1',1
insert into data select 'BO E0KK-03 COBALT','WHSE5000','CAB E9',1
go 

select * from data
go
SQL I tried
SELECT itemnmbr, 
       locncode, 
       Count(*) 
FROM   data 
GROUP  BY itemnmbr, 
          locncode 
go
Result

Change your partition to LOCNCODE, ITEMNMBR with the order by as DestBin...

ROW_NUMBER() OVER(PARTITION BY LOCNCODE, ITEMNMBR ORDER BY DestBin)

This will generate the row number for each ITEMNMBR in each LOCNCODE by the DestBin...

Thank you, changing to (Partition by LOCNCODE, ITEMNMBR ORDER BY DestBin) worked perfectly!