SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
patelmohamad
patelmohamad
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 765
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
Attachments
Reset Number.PNG (87 views, 72.00 KB)
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28336 Visits: 39956
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
patelmohamad
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 765
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28336 Visits: 39956
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
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2056 Visits: 10370
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




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
patelmohamad
patelmohamad
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 765
Thanks Chris for Passing a Logic

the way I needed,

thanks once again

Patel Mohamad
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search