Help with update - numbering based on date

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

Help with update - numbering based on date

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2010-02-10 : 07:35:59

I have a table that must be updated based on a datetime column.
The lowest (registered)datetime for every id should have 1 and so on.

table1
id int (pk)
registered datetime
col varchar(10)
order int

Output:
1 2010-01-01 'Test1' 1
1 2010-01-10 'Test1_1' 2
2 2010-02-02 'Test2' 1
1 2010-01-15 'Test1_1' 3

Kristen
Test

22859 Posts

Posted - 2010-02-10 : 07:40:49

Are you using SQL 2005 or later? If so

ROW_NUMBER() OVER ...

should do the trick

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-10 : 07:40:58

which column do you want to update?

Harsh Athalye
http://www.letsgeek.net/

maevr
Posting Yak Master

169 Posts

Posted - 2010-02-10 : 07:43:30

The column I want to update is the order column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 08:36:16

[code]
UPDATE t
SET [order]= rn
FROM(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [col]) AS rn,[order]
FROM table1)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-10 : 09:29:30

quote:
Originally posted by maevr

The column I want to update is the order column.

It is better you do it via SELECT statement. Otherwise everytime data aredded you need to update the table

Madhivanan

Failing to plan is Planning to fail

maevr
Posting Yak Master

169 Posts

Posted - 2010-02-10 : 09:49:46

Thank you all for the fast replies.

madhivanan: How would you write the select statement solving this issue?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 09:58:55

quote:
Originally posted by maevr

Thank you all for the fast replies.

madhivanan: How would you write the select statement solving this issue?

Madhi is suggesting not to store the order in table but to generate it on the fly on your select like below:-


SELECT id,registered,col,[order]
FROM(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [col]) AS [order],*
FROM table1)t

------------------------------------------------------------------------------------------------------
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources