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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Reset the Number by Column Value using...

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

Rate Topic

Display Mode

Topic Options

Author

Message

patelmohamad

Posted Tuesday, October 16, 2012 5:45 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:18 AM
Points: 52, Visits: 685

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 (18 views, 72.52 KB)

Post #1373167

Lowell

Posted Tuesday, October 16, 2012 5:50 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 11,605, Visits: 27,646

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

patelmohamad

Posted Tuesday, October 16, 2012 6:06 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:18 AM
Points: 52, Visits: 685

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

Lowell

Posted Tuesday, October 16, 2012 6:26 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 11,605, Visits: 27,646

patelmohamad (10/16/2012)

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

ChrisM@home

Posted Tuesday, October 16, 2012 7:03 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 920, Visits: 3,730

Lowell (10/16/2012)

patelmohamad (10/16/2012)

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

patelmohamad

Posted Tuesday, October 16, 2012 11:20 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:18 AM
Points: 52, Visits: 685

Thanks Chris for Passing a Logic

the way I needed,

thanks once again

Patel Mohamad

Post #1373626

« Prev Topic | Next Topic »

Permissions