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.
Author |
Topic |
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2012-12-22 : 04:02:20
|
I have created a very simple view with the following columns:DateNameChangeI want to create a further column of change number, where it would be populated with consecutive numbers where the Date and Name match, eg12/12/12, Grant, Change, 112/12/12, Grant, Change, 212/12/12, Grant, Change, 312/12/12, John, Change, 112/12/12, John, Change, 213/12/12, Grant, Change, 113/12/12, John, Change, 113/12/12, John, Change, 2Can anyone advise if this is possible, and how I would go about it?Thanks in advance |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-22 : 04:13:49
|
[code]SELECT [Date],[Name],[Change],ROW_NUMBER() OVER (PARTITION BY [Date],[Name] ORDER BY [Date]) AS SeqFROM table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2013-01-04 : 05:01:40
|
After the festive period I have finally gotten around to trying your code out.It works fine when the code is run in a query, however is it possible for it to be used in a view?Anytime I try to create a view using this code, management studio crashes. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-04 : 05:03:55
|
hmm..thats strange. this can be used in a view as wellBut you need to create view usingCREATE VIEW viewNameASSELECT [Date],[Name],[Change],ROW_NUMBER() OVER (PARTITION BY [Date],[Name] ORDER BY [Date]) AS SeqFROM table in SQL Server query window in management studio and should not use view editor------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sprotson
Yak Posting Veteran
75 Posts |
Posted - 2013-01-04 : 05:16:08
|
ThanksI was trying to create the view by selecting 'New View' then saving it.I have managed to create the view using your code. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-04 : 05:42:21
|
welcomethe other view editor wont support some of new syntax------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|