Row_Number() Restart - SQL Server Forums

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)

Row_Number() Restart

Author

Topic

lgameiro
Starting Member

2 Posts

Posted - 2013-11-19 : 10:13:14

Hi,

I have a small query to do, and i'm playing with Row_Number().

I will try to give a easy example:

Col1 Col2
2013-09-22 09:49:47.363 AAA
2013-09-29 12:52:06.843 BBBBBB
2013-11-12 13:14:20.727 AAA
2013-11-12 13:19:37.980 AAA
2013-11-12 13:38:29.967 BBBBBB
2013-11-18 15:54:25.523 AAA
2013-11-18 15:54:39.120 AAA
2013-11-18 15:55:02.063 AAA

What i want is:

RowNb Col1 Col2
1 2013-09-22 09:49:47.363 AAA
1 2013-09-29 12:52:06.843 BBBBBB
1 2013-11-12 13:14:20.727 AAA
2 2013-11-12 13:19:37.980 AAA
1 2013-11-12 13:38:29.967 BBBBBB
1 2013-11-18 15:54:25.523 AAA
2 2013-11-18 15:54:39.120 AAA
3 2013-11-18 15:55:02.063 AAA

I already try very possibilities, and nothing give me this, cause every time that finds AAA he continues with the previous number, so stays like:

1 2013-09-22 09:49:47.363 AAA
1 2013-09-29 12:52:06.843 BBBBBB
2 2013-11-12 13:14:20.727 AAA
3 2013-11-12 13:19:37.980 AAA
2 2013-11-12 13:38:29.967 BBBBBB
4 2013-11-18 15:54:25.523 AAA
5 2013-11-18 15:54:39.120 AAA
6 2013-11-18 15:55:02.063 AAA

Query:
Select ROW_NUMBER() over(PARTITION BY Col2 order by Col1) as NbRow,Col1,Col2
From <table>

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-19 : 10:40:50

The output you are getting is consistent with the query you posted. Perhaps this is what you want?

Select ROW_NUMBER() over(PARTITION BY Col2, CAST(Col1 AS DATE) order by Col1) as NbRow,Col1,Col2
From <table>

lgameiro
Starting Member

2 Posts

Posted - 2013-11-19 : 10:52:24

Thanks James,

But Col1 (the date) cant go in partition, cause i need the hours and seconds.

What i'm asking, if its possible with ROw_Number(), ordering by the date, and every time that the other column changes, restart the row_number

So AAA - BBBBBB - AAA - AAA - BBBBBB gives in Row_Number() 1 - 1 - 1 - 2 - 1 and not 1 - 1 - 2 - 3 - 2

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-19 : 14:21:51

You can do something like shown below - I may not have the exact logic, but the goal is that you want to partition by your break points first, and then get the rownumbers on that partitioned set

SELECT
	col1, col2,
	ROW_NUMBER() OVER(PARTITION BY col2,N ORDER BY col1) AS RN
FROM 
(
SELECT
	*,
	ROW_NUMBER() OVER (ORDER BY col1)-
	ROW_NUMBER() OVER (PARTITION BY col2 ORDER BY col1) AS N
FROM <table>
)s 
ORDER BY col1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 02:34:09

quote:
Originally posted by lgameiro

Thanks James,

But Col1 (the date) cant go in partition, cause i need the hours and seconds.

What i'm asking, if its possible with ROw_Number(), ordering by the date, and every time that the other column changes, restart the row_number

So AAA - BBBBBB - AAA - AAA - BBBBBB gives in Row_Number() 1 - 1 - 1 - 2 - 1 and not 1 - 1 - 2 - 3 - 2

Why? whats the issue
I think previously posted suggestion will give you exactly your output. You can still get hours minutes etc from Col1 even if you used it within PARTITION BY
see this illustration.


declare @t table
(
Col1 datetime,
Col2 varchar(10)
)
insert @t
values('2013-09-22 09:49:47.363','AAA'),
('2013-09-29 12:52:06.843','BBBBBB'),
('2013-11-12 13:14:20.727','AAA'),
('2013-11-12 13:19:37.980','AAA'),
('2013-11-12 13:38:29.967','BBBBBB'),
('2013-11-18 15:54:25.523','AAA'),
('2013-11-18 15:54:39.120','AAA'),
('2013-11-18 15:55:02.063','AAA')

select ROW_NUMBER() over (PARTITION BY Col2,DATEDIFF(dd,0,Col1) ORDER BY Col1) AS RowNb,Col1,DATEPART(hh,Col1) AS hourPart,DATEPART(ss,Col1) AS SecondsPart ,Col2
FROM @t
ORDER BY Col1


output
------------------------------------------------------------------
RowNb	Col1	                Col2	hourPart	SecondsPart
------------------------------------------------------------------
1	2013-09-22 09:49:47.363	AAA	9	        47
1	2013-09-29 12:52:06.843	BBBBBB	12	         6
1	2013-11-12 13:14:20.727	AAA	13	        20
2	2013-11-12 13:19:37.980	AAA	13	        37
1	2013-11-12 13:38:29.967	BBBBBB	13	        29
1	2013-11-18 15:54:25.523	AAA	15	        25
2	2013-11-18 15:54:39.120	AAA	15	        39
3	2013-11-18 15:55:02.063	AAA	15	         2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources