CODEWITHSUNDEEP
sql-serverpartition
Mark Corker
Mark Corker

Reputation: 3

SQL Newbie - Over Partition?

I have the following query. I am trying to get the Row # to increment whenever the value in Value1 field changes. The SensorData table has 2800 records and the Value1 is either 0 or 3 and changes throughout the day.

SELECT 
    ROW_NUMBER() OVER(PARTITION BY Value1 ORDER BY Block ASC) AS Row#,
    GatewayDetailID, Block, Value1
FROM            
    SensorData
ORDER BY
    Row#

I get the following results:

enter image description here

It seems like it creates only 2 partitions 0 and 3. It is not restarting the row number every time the value 1 changes.?

Upvotes: 0

Views: 72

Answers (1)

JMabee
JMabee

Reputation: 2300

First instead of creating a permanent table I just changed it to a Temp table.

So, Given your example here is what I came up with:

WITH CTE as(
select ROW_NUMBER() OVER(ORDER BY BLOCK) RN, LAG(Value1,1,VALUE1) OVER (ORDER BY BLOCK) LG, 
GatewayDetailID, Block, Value1,Value2,Vaule3
 from #tmp
), 
CTE2 as (
    select *, CASE WHEN LG <> VALUE1 THEN RN ELSE 0 END RowMark 
    from cte
    ), 
CTE3 AS (
    select MIN(Block) BL, RowMark from CTE2
    GROUP BY ROwMark
    ),
CTE4 AS (
    SELECT GatewayDetailID,Block,Value1,Value2,Vaule3,RMM from cte2 t1
    CROSS APPLY (SELECT MAX(ROWMark) RMM FROM CTE3 t9 where t1.Block >= t9.ROwMark and t1.RN >= t9.RowMark) t2
    )

SELECT GateWayDetailID,Block,Value1,Value2,Vaule3, ROW_NUMBER() OVER(Partition by RMM ORDER BY BLOCK) RN
FROM CTE4
ORDER BY BLOCK

I first had to get a Row number for all the rows, then depending on when the Value1 changed I marked that as a new group. From that I created a CTE with the date and row boundry for each group. And then lastly I cross applied that back to the table to find each row in each group.

From that last CTE I merely just applied a simple ROW_NUMBER() function portioned by each RowMarker group and poof....row numbers.

There may be a better way to do this, but this was how I logically worked through the problem.

Upvotes: 1

Related Questions