Reset the Number by Column Value using ROW_NUMBER() OVER (PARTITION BY Field)

Question

Post reply

Reset the Number by Column Value using ROW_NUMBER() OVER (PARTITION BY Field)

patelmohamad

SSCommitted

Points: 1873
More actions
October 16, 2012 at 5:45 am

#98367

Hi Guys
Need your help
/*
SELECT
IDENTITY(BIGINT ,1 ,1) AS RowID
,CAST( '' AS DATETIME) AS TodaysDate
,CAST( '' AS BIGINT) AS RowNumber
,CAST( '' AS VARCHAR(100)) Name
INTO DropTable1
TRUNCATE TABLE DropTable1
--*/
/*
INSERT INTO DropTable1 VALUES(GETDATE() ,1 ,'Test') -- Insert for 10 times
INSERT INTO DropTable1 VALUES(GETDATE() ,2 ,'Test') -- Insert for 10 times
INSERT INTO DropTable1 VALUES(GETDATE() ,1 ,'Test') -- Insert for 05 times
INSERT INTO DropTable1 VALUES(GETDATE() ,2 ,'Test') -- Insert for 05 times
--*/
SELECT * FROM DropTable1
SELECT
ROW_NUMBER() OVER (PARTITION BY RowNumber order by RowID)
,RowID
,TodaysDate
,Name
,RowNumber
FROM
DropTable1
ORDER BY RowID
Output i want described in Attachment
Thanks
Patel Mohamad

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

patel i see what you are asking, but i don't see the data reflecting something that allows it to beswitched like that;

about all i can suggest is a UNION and statements that reflect the desired ranges.

;

SELECT

ROW_NUMBER() OVER (PARTITION BY RowNumber order by RowID)

,RowID

,TodaysDate

,Name

,RowNumber

where rowid <=20

UNION ALL

SELECT

ROW_NUMBER() OVER (PARTITION BY RowNumber order by RowID)

,RowID

,TodaysDate

,Name

,RowNumber

where rowid > 20

and rowid <= 25

UNION ALL

SELECT

ROW_NUMBER() OVER (PARTITION BY RowNumber order by RowID)

,RowID

,TodaysDate

,Name

,RowNumber

where rowid > 25

and rowid <= 30

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

patelmohamad SSCommitted Points: 1873 More actions · Answer 2

Thanks Lowell for instant reply,

This is my predication, not the actual data

, actual data is differ from my Example

in my data it gives me lot of no's 1,2,3.......n or sometime it gives only 2, 3 no's

in such case i cant specify the RowId.

any other technique on the same query.

Thanks once again.

Patel Mohamad

Lowell SSC Guru Points: 323518 More actions · Answer 3

patelmohamad (10/16/2012)
Thanks Lowell for instant reply,
This is my predication, not the actual data
, actual data is differ from my Example
in my data it gives me lot of no's 1,2,3.......n or sometime it gives only 2, 3 no's
in such case i cant specify the RowId.
any other technique on the same query.
Thanks once again.

so far, I have no idea;

can you explain WHY the row_number could/should reset?

if you can explain that, we could change the partition by predicate to match the desired logic.

my problem, when reviewing the sample data in the screenshot, was it looked like you wanted to break based on # or rows (20 rows, then 5 rows, then 5 rows)

and not break based on something in the data itself.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

Lowell (10/16/2012)
patelmohamad (10/16/2012)
Thanks Lowell for instant reply,
This is my predication, not the actual data
, actual data is differ from my Example
in my data it gives me lot of no's 1,2,3.......n or sometime it gives only 2, 3 no's
in such case i cant specify the RowId.
any other technique on the same query.
Thanks once again.
so far, I have no idea;
can you explain WHY the row_number could/should reset?
if you can explain that, we could change the partition by predicate to match the desired logic.
my problem, when reviewing the sample data in the screenshot, was it looked like you wanted to break based on # or rows (20 rows, then 5 rows, then 5 rows)
and not break based on something in the data itself.

I reckon it's one of these, Lowell:

SELECT

*,

NewRowNumber = DENSE_RANK() OVER(PARTITION BY RowNumber, Grouper ORDER BY TodaysDate)

FROM (

SELECT

*,

Grouper = RowID - ROW_NUMBER() OVER(ORDER BY RowNumber, RowID)

FROM DropTable1

) d

ORDER BY RowID

Used for e.g. contiguous date range identification.

Here's a mod of the test script:

SELECT

IDENTITY(BIGINT ,1 ,1) AS RowID

,CAST( '' AS DATETIME) AS TodaysDate

,CAST( '' AS BIGINT) AS RowNumber

,CAST( '' AS VARCHAR(100)) Name

INTO DropTable1

TRUNCATE TABLE DropTable1

GO

INSERT INTO DropTable1 VALUES(GETDATE() ,1 ,'Test') -- Insert for 10 times

WAITFOR DELAY '00:00:00.700'

GO 10

INSERT INTO DropTable1 VALUES(GETDATE() ,2 ,'Test') -- Insert for 10 times

WAITFOR DELAY '00:00:00.700'

GO 10

INSERT INTO DropTable1 VALUES(GETDATE() ,1 ,'Test') -- Insert for 05 times

WAITFOR DELAY '00:00:00.700'

GO 5

INSERT INTO DropTable1 VALUES(GETDATE() ,2 ,'Test') -- Insert for 05 times

WAITFOR DELAY '00:00:00.700'

GO 5

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

patelmohamad SSCommitted Points: 1873 More actions · Answer 5

Thanks Chris for Passing a Logic

the way I needed,

thanks once again

Patel Mohamad