Assigning an Order Number to column

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

Assigning an Order Number to column

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-21 : 13:04:23

I have this table (the red number is the number I'm trying to assign)
[CODE]Position Invoice
9 18001 1
11 18001 2
159 18004 1
259 18006 1
261 18006 2
263 18006 3[/CODE]I need to assign to each record its ordinal number, grouped by Invoice and ordered by position.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-21 : 13:13:01

If you are on SQL 2005 or later you can use the row_number function like this:

SELECT
	Position,
	Invoice,
	ROW_NUMBER() OVER (PARTITION BY Invoice ORDER BY Position) AS OrdinalNumber
FROM
	YourTable

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-21 : 13:24:26

Great!!! Thank you sunitabeck

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-23 : 08:30:10

See what you can do with row_number() function
http://beyondrelational.com/modules/2/blogs/70/posts/10802/multipurpose-rownumber-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources