SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Strange behavior of ROW_NUMBER (partition by over)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Calkins
Starting Member

5 Posts

Posted - 12/30/2012 :  09:40:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/30/2012 :  10:29:16  Show Profile  Reply with Quote
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
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
788 Posts

Posted - 12/30/2012 :  10:31:20  Show Profile  Reply with Quote
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.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
335 Posts

Posted - 12/31/2012 :  11:56:06  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000