SQL Server Forums
Profile | Register | 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
 New Topic  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
52325 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
52325 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
52325 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  
 New 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.12 seconds. Powered By: Snitz Forums 2000