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
 General SQL Server Forums
 New to SQL Server Programming
 View - Reference Number

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:

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

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 Seq
FROM table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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 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/

Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 2013-01-04 : 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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 05:42:21
welcome
the other view editor wont support some of new syntax

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -