Strange behavior of ROW_NUMBER (partition by over)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Strange behavior of ROW_NUMBER (partition by over)

Author

Topic

Calkins
Starting Member

5 Posts

Posted - 2012-12-30 : 09:40:23

I thought that function ROW_NUMBER with PARTITION works different for example:


SELECT * INTO #tmp_GridResults_1
FROM (
SELECT N'13' AS [State_ID], N'2012-10-23 00:00:00.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 00:00:01.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 00:00:12.727' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 00:43:59.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 04:00:11.467' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 04:01:20.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:00:11.430' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:20.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:33.297' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:45.803' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 05:23:46.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 05:23:58.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:58.160' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:24:10.990' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:11.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:25.973' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:26.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:40.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-24 00:00:00.000' AS [Value_Time], N'0' AS [Value] ) t;
SELECT [State_ID], [Value_Time], [Value]
FROM #tmp_GridResults_1
ORDER BY Value_Time

SELECT
    State_ID
    ,Value_Time   
    ,ROW_NUMBER() OVER (PARTITION BY State_ID ORDER BY Value_Time ) AS RowNumber_Partition

FROM
    #tmp_GridResults_1
ORDER BY
    Value_Time

DROP TABLE #tmp_GridResults_1
GO

gives me this result:


State_ID	Value_Time	RowNumber_Partition         I_thought_will_be_this
13	2012-10-23 00:00:00.000	1                           1
13	2012-10-23 00:00:01.000	2                           2
5	2012-10-23 00:00:12.727	1 -> new State_ID           1
13	2012-10-23 00:43:59.000	3 -> new State_ID           1 
13	2012-10-23 04:00:11.467	4                           2
13	2012-10-23 04:01:20.000	5                           3
5	2012-10-23 05:00:11.430	2 -> new State_ID           1
5	2012-10-23 05:23:20.000	3                           2
5	2012-10-23 05:23:33.297	4                           3
5	2012-10-23 05:23:45.803	5                           4  
13	2012-10-23 05:23:46.000	6 -> new State_ID           1
13	2012-10-23 05:23:58.000	7                           2
5	2012-10-23 05:23:58.160	6 -> new State_ID           1
5	2012-10-23 05:24:10.990	7                           2
27	2012-10-23 05:24:11.000	1 -> new State_ID           1
27	2012-10-23 05:24:25.973	2                           2
27	2012-10-23 05:24:26.000	3                           3
27	2012-10-23 05:24:40.000	4                           4
5	2012-10-24 00:00:00.000	8 -> new State_ID           1

I thought PARTITION BY reset count for every time State_ID change?

How can i get results like in column "I_thought_will_be_this"?

Thx in advance for help.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-30 : 10:29:16

Do this

SELECT identity(int,1,1) ID ,* INTO #tmp_GridResults_1
FROM (
SELECT N'13' AS [State_ID], N'2012-10-23 00:00:00.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 00:00:01.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 00:00:12.727' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 00:43:59.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 04:00:11.467' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 04:01:20.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:00:11.430' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:20.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:33.297' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:45.803' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 05:23:46.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'13' AS [State_ID], N'2012-10-23 05:23:58.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:23:58.160' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-23 05:24:10.990' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:11.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:25.973' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:26.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'27' AS [State_ID], N'2012-10-23 05:24:40.000' AS [Value_Time], N'0' AS [Value] UNION ALL
SELECT N'5' AS [State_ID], N'2012-10-24 00:00:00.000' AS [Value_Time], N'0' AS [Value] ) t;


; With CTE as
(
SELECT
    State_ID
    ,Value_Time   
    ,ROW_NUMBER() OVER (PARTITION BY State_ID ORDER BY Value_Time ) AS RowNumber_Partition
    ,ID,ID - ROW_NUMBER() OVER (PARTITION BY State_ID ORDER BY Value_Time ) as Difference

FROM
    #tmp_GridResults_1
)

Select *,ROW_NUMBER() OVER (PARTITION BY State_ID,Difference Order by Difference desc) as Seq
from CTE
Order by ID


State_ID	Value_Time	RowNumber_Partition	ID	Difference	Seq
13	2012-10-23 00:00:00.000	1	1	0	1
13	2012-10-23 00:00:01.000	2	2	0	2
5	2012-10-23 00:00:12.727	1	3	2	1
13	2012-10-23 00:43:59.000	3	4	1	1
13	2012-10-23 04:00:11.467	4	5	1	2
13	2012-10-23 04:01:20.000	5	6	1	3
5	2012-10-23 05:00:11.430	2	7	5	1
5	2012-10-23 05:23:20.000	3	8	5	2
5	2012-10-23 05:23:33.297	4	9	5	3
5	2012-10-23 05:23:45.803	5	10	5	4
13	2012-10-23 05:23:46.000	6	11	5	1
13	2012-10-23 05:23:58.000	7	12	5	2
5	2012-10-23 05:23:58.160	6	13	7	1
5	2012-10-23 05:24:10.990	7	14	7	2
27	2012-10-23 05:24:11.000	1	15	14	1
27	2012-10-23 05:24:25.973	2	16	14	2
27	2012-10-23 05:24:26.000	3	17	14	3
27	2012-10-23 05:24:40.000	4	18	14	4
5	2012-10-24 00:00:00.000	8	19	11	1

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-12-30 : 10:31:20

You are partitioning the data by State_ID - based on the order of the value_time within each state_id. If you change your query and order the results by State_ID and Value_Time instead of just Value_Time you will see why the row_number function is giving you those values.

If you need the results you have outlined - you will need something else to identify how the data should be partitioned.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2012-12-31 : 11:56:06

quote:
I thought PARTITION BY reset count for every time State_ID change?

The PARTITION BY is partitioning the table, not the rows in sequence. So, for a single PB column, all rows with a given column value will fall into a single partition.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources