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.
| Author |
Topic |
|
shajimanjeri
Posting Yak Master
179 Posts |
 Posted - 2007-06-18 : 07:58:12
|
| I have a table named as C1_Messages with fields Id, Messages, Dates.My Id filed looks like this:1000100110081009108410931098 etc.But here I need to make these Ids in a order, means it sould be in a consecutive order. Like 100010011002100310041005 etc.So is there anyway to do this method in SQL?RegardsShaji |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-06-18 : 08:05:42
|
| Sure. You can use ORDER BY clause in your SELECT statement.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
shajimanjeri
Posting Yak Master
179 Posts |
Posted - 2007-06-18 : 08:54:35
|
| But I need the Ids to be update and set all these Ids in consecutive orders. Like 1000 for first row, 1001 for second row, 1003 for third row, 1004 for fourth row ete to last record. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-06-18 : 23:21:33
|
| In SQL Server 2005 you can probably put together an update statement from a select of your existing IDs plus the row_number() function.Not something I'd recommend though. An ID is hardly and ID if it changes. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-06-19 : 02:50:21
|
| try this (untested)UPDATE tset t.ID = t.recidfrom (select 999 + row_number() over (order by id) as recid from C1_Messages ) as tPeter LarssonHelsingborg, Sweden |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-20 : 05:23:27
|
| Where do you want to show data?Sounds that you want to generate Serial NoMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Ordering Ids