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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 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
819 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
Aged Yak Warrior

USA
550 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  
 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.08 seconds. Powered By: Snitz Forums 2000