Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 View - Reference Number
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sprotson
Yak Posting Veteran

75 Posts

Posted - 12/22/2012 :  04:02:20  Show Profile  Reply with Quote
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
52326 Posts

Posted - 12/22/2012 :  04:13:49  Show Profile  Reply with Quote

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/

Go to Top of Page

sprotson
Yak Posting Veteran

75 Posts

Posted - 01/04/2013 :  05:01:40  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 01/04/2013 :  05:03:55  Show Profile  Reply with Quote
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 - 01/04/2013 :  05:16:08  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 01/04/2013 :  05:42:21  Show Profile  Reply with Quote
welcome
the other view editor wont support some of new syntax

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000