| Author |
 Topic  |
|
|
sprotson
Yak Posting Veteran
70 Posts |
 Posted - 12/22/2012 : 04:02:20
|
I have created a very simple view with the following columns:
Date
Name
Change
I want to create a further column of change number, where it would be populated with consecutive numbers where the Date and Name match, eg
12/12/12, Grant, Change, 1
12/12/12, Grant, Change, 2
12/12/12, Grant, Change, 3
12/12/12, John, Change, 1
12/12/12, John, Change, 2
13/12/12, Grant, Change, 1
13/12/12, John, Change, 1
13/12/12, John, Change, 2
Can anyone advise if this is possible, and how I would go about it?
Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 12/22/2012 : 04:13:49
|
SELECT [Date],[Name],[Change],ROW_NUMBER() OVER (PARTITION BY [Date],[Name] ORDER BY [Date]) AS Seq
FROM table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
sprotson
Yak Posting Veteran
70 Posts |
Posted - 01/04/2013 : 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
India
47173 Posts |
Posted - 01/04/2013 : 05:03:55
|
hmm..thats strange. this can be used in a view as well
But you need to create view using
CREATE VIEW viewName
AS
SELECT [Date],[Name],[Change],ROW_NUMBER() OVER (PARTITION BY [Date],[Name] ORDER BY [Date]) AS Seq
FROM table
in SQL Server query window in management studio and should not use view editor
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sprotson
Yak Posting Veteran
70 Posts |
Posted - 01/04/2013 : 05:16:08
|
Thanks
I 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
India
47173 Posts |
Posted - 01/04/2013 : 05:42:21
|
welcome
the other view editor wont support some of new syntax
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
View - Reference Number
