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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]