View - Reference Number - 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

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/

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

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.

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources