Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reset the Number by Column Value using ROW_NUMBER() OVER (PARTITION BY Field) Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 5:45 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:51 AM
Points: 52, Visits: 723
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

  Post Attachments 
Reset Number.PNG (21 views, 72.52 KB)
Post #1373167
Posted Tuesday, October 16, 2012 5:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:07 PM
Points: 12,910, Visits: 32,028
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1373170
Posted Tuesday, October 16, 2012 6:06 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:51 AM
Points: 52, Visits: 723
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
Post #1373184
Posted Tuesday, October 16, 2012 6:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:07 PM
Points: 12,910, Visits: 32,028
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1373194
Posted Tuesday, October 16, 2012 7:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 1,090, Visits: 6,552
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
Post #1373225
Posted Tuesday, October 16, 2012 11:20 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:51 AM
Points: 52, Visits: 723
Thanks Chris for Passing a Logic

the way I needed,

thanks once again


Patel Mohamad
Post #1373626
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse