SQLTeam.com | Weblogs | Forums

Dense_rank() row number?


#1

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.


#2

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


#3

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


#4

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